Multiple CRITERIA in SUMIFS

[kriggs]

Hi,

Taking this example as base:
http://www.alchemex.com/blog/excel-t...mifs-function/

If I wanted to SUMIFS all the January and February of the East Coast, would it be possible just by adding something on criteria 1?

Thanks.

[BB1972]

kriggs, welcome to the forum. You would add a 2nd criteria, by using a comma after "criteria1".

Hope this helps.

[kbkumar]

Perhaps use Sumproduct:

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

Rather than using the wizard for building a SUMIFS function one way:

=SUMIFS(C4:C17,A4:A17,"January",B4:B17,"East Coast")+SUMIFS(C4:C17,A4:A17,"February",B4:B17,"East Coast")

[JosephP]

another way
=SUM(SUMIFS(C4:C17,A4:A17,{"January","February"},B4:B17,"East Coast"))

[Jacc]

I assume that you are aware that Pivot Tables are very efficient at this type of work. Just checking.
This array formula will also work:

Please Login or Register  to view this content.

[daddylonglegs]

=SUM(IF((A4:A17="January")+(A4:A17="February")*(B4:B17="East Coast"),B4:C17))

Note that you need some extra parentheses to guarantee the correct result, i.e.

=SUM(IF(((A4:A17="January")+(A4:A17="February"))*(B4:B17="East Coast"),C4:C17))

[Tony Valko]

And just for the heck of it, here's another way to write that:

=SUM(IF(A4:A17={"January","February"},IF(B4:B17="East Coast",C4:C17)))

Array entered.

[kriggs]

Thanks everyone, kbkumar and Richard Buttrey, your options work but my main SUMIFS formula is already 150chars long, and I need it the last criteria to evaluate to 8 diferent words, so the cell would be with around 1200chars, something impractical.

JosephP, BB1972, it worked, but only with static values, how could I evaluate the values to a cell, ex. leaving January and Fabruary in D1 and D2, and the criteria be {D1,D2}? This throws me an error.

Jacc, daddylonglegs and Tony, I've tried alot but those aren't working, they never sum for me, it gives a !value ou 0

[kbkumar]

.... I need it the last criteria to evaluate to 8 diferent words, so the cell would be with around 1200chars, something impractical.

Would you be able to list all the criterias?

On the face of it, perhaps a VBA code would be easy it seems.

[JosephP]

you can use sumproduct then
=SUMPRODUCT((ISNUMBER(MATCH(A4:A17,D1:D15,0)))*(B4:B17="East Coast")*C4:C17)

[kriggs]

Would you be able to list all the criterias?

On the face of it, perhaps a VBA code would be easy it seems.

For the time beeing this is the criterias, texts and formulas are in portuguese
SoMASES = SUMIFS
; = ,
=SOMASES('Movimentacao Estacas'!$G$1:$G$40;'Movimentacao Estacas'!$F$1:$F$40;C$2;'Movimentacao Estacas'!$E$1:$E$40;$A3;'Movimentacao Estacas'!$D$1:$D$40;Estoque!$A$1:$K$1)

It's 4 criterias, just that one left for it to be functional.

That's what I needed JoshepP, I'll try to implement the other criterias in it now.

---------------------
JoshephP

Managed to make it work, but it doens't accept a table collunm name as entry, ex.: "Table1[Region]", no problem tough, thanks!

[JosephP]

it will take a column name-how did you do it and what problem did you have?

[Richard Buttrey]

Thanks everyone, kbkumar and Richard Buttrey, your options work but my main SUMIFS formula is already 150chars long, and I need it the last criteria to evaluate to 8 diferent words, so the cell would be with around 1200chars, something impractical.

Hi,

In that case have you considered a simple autofilter on your data, filtering the columns as necessary and having an

=SUBTOTAL(9,C4:C17)

in say C2.

[Grimace]

With the range formula in the above options, use Ctrl+Alt+Enter when entering into the cell, this should fix your problem.