If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SUMIF with multiple columns in sum range
I have an array that I need to sum multiple columns depending of the value in
Col D. The ws containing the array is called 'data'. I must sum cols F,J,N,R,V,Z in rows 7-25. On another ws my formula is: =SUMIF(Data!$D$7:$D$25,$A4,Data!$F$7:$F$25) Of course this only gives me the sum of Col F. Is there a way to put the sum range in one formula? I want to avoid stringing a bunch of SUMIFs together. Thanks, Joe M |
#2
|
|||
|
|||
SUMIF with multiple columns in sum range
One way...
Assumes no TEXT entries in cols F,J,N,R,V,Z. =SUMPRODUCT(--($D$7:$D$25=$A4),$F$7:$F$25+$J$7:$J$25+$N$7:$N$25+ $R$7:$R$25+$V$7:$V$25+$Z$7:$Z$25) -- Biff Microsoft Excel MVP "Joe M." wrote in message ... I have an array that I need to sum multiple columns depending of the value in Col D. The ws containing the array is called 'data'. I must sum cols F,J,N,R,V,Z in rows 7-25. On another ws my formula is: =SUMIF(Data!$D$7:$D$25,$A4,Data!$F$7:$F$25) Of course this only gives me the sum of Col F. Is there a way to put the sum range in one formula? I want to avoid stringing a bunch of SUMIFs together. Thanks, Joe M |
#3
|
|||
|
|||
SUMIF with multiple columns in sum range
This works. I tried to make it shorter using named ranges in the formula but
I get a #VALUE error. My named range is called Data_Sel_WA. My formula is now: =SUMPRODUCT(--(Data_Selection!$D$7:$D$25=$A10),Data_Sel_WA) My named range is defined as: =Data_Selection!$F$7:$F$25,Data_Selection!$J$7:$J$ 25,Data_Selection!$N$7:$N$25,Data_Selection!$R$7:$ R$25,Data_Selection!$V$7:$V$25,Data_Selection!$Z$7 :$Z$25,Data_Selection!$AD$7:$AD$25 Did I do something wrong or will named ranges not work with SUMPRODUCT? Thanks, Joe M. "T. Valko" wrote: One way... Assumes no TEXT entries in cols F,J,N,R,V,Z. =SUMPRODUCT(--($D$7:$D$25=$A4),$F$7:$F$25+$J$7:$J$25+$N$7:$N$25+ $R$7:$R$25+$V$7:$V$25+$Z$7:$Z$25) -- Biff Microsoft Excel MVP "Joe M." wrote in message ... I have an array that I need to sum multiple columns depending of the value in Col D. The ws containing the array is called 'data'. I must sum cols F,J,N,R,V,Z in rows 7-25. On another ws my formula is: =SUMIF(Data!$D$7:$D$25,$A4,Data!$F$7:$F$25) Of course this only gives me the sum of Col F. Is there a way to put the sum range in one formula? I want to avoid stringing a bunch of SUMIFs together. Thanks, Joe M . |
#4
|
|||
|
|||
SUMIF with multiple columns in sum range
In this application, each of these:
=Data_Selection!$F$7:$F$25,Data_Selection!$J$7:$J $25,Data_Selection!$N$7:$N$25,Data_Selection!$R$7: $R$25,Data_Selection!$V$7:$V$25,Data_Selection!$Z$ 7:$Z$25,Data_Selection!$AD$7:$AD$25 Needs to be a *separate* range. You can use this array formula** where the range is a single contiguous range but only the specific columns in that range will be calculated: =SUM(IF(Selection!D725=A10,IF(MOD(COLUMN(Selecti on!F7:AD25)-COLUMN(Selection!F7),4)=0,Selection!F7:AD25))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Joe M." wrote in message ... This works. I tried to make it shorter using named ranges in the formula but I get a #VALUE error. My named range is called Data_Sel_WA. My formula is now: =SUMPRODUCT(--(Data_Selection!$D$7:$D$25=$A10),Data_Sel_WA) My named range is defined as: =Data_Selection!$F$7:$F$25,Data_Selection!$J$7:$J$ 25,Data_Selection!$N$7:$N$25,Data_Selection!$R$7:$ R$25,Data_Selection!$V$7:$V$25,Data_Selection!$Z$7 :$Z$25,Data_Selection!$AD$7:$AD$25 Did I do something wrong or will named ranges not work with SUMPRODUCT? Thanks, Joe M. "T. Valko" wrote: One way... Assumes no TEXT entries in cols F,J,N,R,V,Z. =SUMPRODUCT(--($D$7:$D$25=$A4),$F$7:$F$25+$J$7:$J$25+$N$7:$N$25+ $R$7:$R$25+$V$7:$V$25+$Z$7:$Z$25) -- Biff Microsoft Excel MVP "Joe M." wrote in message ... I have an array that I need to sum multiple columns depending of the value in Col D. The ws containing the array is called 'data'. I must sum cols F,J,N,R,V,Z in rows 7-25. On another ws my formula is: =SUMIF(Data!$D$7:$D$25,$A4,Data!$F$7:$F$25) Of course this only gives me the sum of Col F. Is there a way to put the sum range in one formula? I want to avoid stringing a bunch of SUMIFs together. Thanks, Joe M . |
Thread Tools | |
Display Modes | |
|
|