Summing across multiple columns using SUMIF function

[kristikls]

I'm trying to get a calculation using SUMIFS that looks at 2 criteria to determine whether a figure should be included in a sum but I want the Sum Range to span multiple columns. I can get the formula to work for a single column but not multiples.


I guess I can't upload the file here but the formula currently is:
=SUMIFS('New Sales'!$N$11:$N$9999,'New Sales'!$E$11:$E$9999,$A$5,'New Sales'!$J$11:$J$9999,1)
I tried to do a named range and that didn't work. Basically, I want where column N is just totally to instead be about 20 columns.

 

[Aladin Akyurek]

I'm trying to get a calculation using SUMIFS that looks at 2 criteria to determine whether a figure should be included in a sum but I want the Sum Range to span multiple columns. I can get the formula to work for a single column but not multiples.


I guess I can't upload the file here but the formula currently is:
=SUMIFS('New Sales'!$N$11:$N$9999,'New Sales'!$E$11:$E$9999,$A$5,'New Sales'!$J$11:$J$9999,1)
I tried to do a named range and that didn't work. Basically, I want where column N is just totally to instead be about 20 columns.

You need to switch to a different type of formula...

Control+shift+enter, not just enter:

=SUM(IF('New Sales'!$E$11:$E$9999=$A$5,IF('New Sales'!$J$11:$J$9999=1,'New Sales'!$N$11:$Q$9999)))