Can I use more complex logical expression for sumif as creteria?

[xwenx]

I want to use a more complex logical expression as creteria in SUMIF. For
example, sum number in the cell on column 6 when cell on column 1 is "MA" AND
cell on column 2 is greater than 5000. I may use either "and" or "or" to
connect the two tests. Is there an easy way to do that without creating an
extra combined column (to make MA5001, MA12400, NH2300, etc.)

 

[Mark Lincoln]

You can use SUMPRODUCT like this:

=SUMPRODUCT(--(A3:A10="MA"),--(B3:B10>5000),F3:F10)

Your state abbreviations are in A3:A10, your numbers to test on are in
B3:B10, and the numbers to sum are in F3:F10 (column 6). Change to
suit your circumstance.

 

[xwenx]

Thank you, Mark.
What if the condition is an "or" (not and). Either the first or the second
condition is met, then do the sum up...

 

[Mark Lincoln]

That complicates matters some. We have to sum the rows in which either
criterion is met and subtract the rows where both are met (otherwise
the latter cases would be counted twice). Either of these work:

=SUMPRODUCT(--(A3:A10="MA"),F3:F10)+SUMPRODUCT(--(B3:B10>5000),F3:F10)-SUMPRODUCT(--(A3:A10="MA"),--(B3:B10>5000),F3:F10)

=SUMIF(A3:A10,"MA",F3:F10)+SUMIF(B3:B10,">5000",F3:F10)-SUMPRODUCT(--(A3:A10="MA"),--(B3:B10>5000),F3:F10)

 

[Biff]

Hi!

Try this (using Marks example):

=SUMPRODUCT(--((A3:A10="MA")+(B3:B10>5000)>0),F3:F10)

Biff

 

[Mark Lincoln]

Good solution, Biff. Nice and neat!

I was trying something similar but got a #VALUE error. Then my
solutions suggested themselves to me and in the interests of time (I do
this during slack moments at work) I dropped my original effort.

Three solutions to one problem. That's why I like this newsgroup.

 

[JE McGimpsey]

This is a bit shorter:

=SUMPRODUCT(--((A3:A10="MA")+(B3:B10>5000)>0),F3:F10)

 

[JE McGimpsey]

Oops, didn't see the identical solution in the other subthread...