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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.)
 
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.
 
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...
 
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)
 
Hi!

Try this (using Marks example):

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

Biff
 
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. :-)
 
Back
Top