SUMIF with multiple columns in sum range

  • Thread starter Thread starter Joe M.
  • Start date Start date
J

Joe M.

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
 
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)
 
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.
 
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
Click to expand...

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!D7:D25=A10,IF(MOD(COLUMN(Selection!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.
 
:bow:[What is the answer to this ?QUOTE=Joe M.;13809939]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[/QUOTE]
 
I want to get the answer to this question. I am working on a problem just like the one mentioned by Joe M
 
Back
Top