software

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 quot;MAquot; AND
cell on column 2 is greater than 5000. I may use either quot;andquot; or quot;orquot; 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=quot;MAquot;),--(B3:B10gt;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 quot;orquot; (not and). Either the first or the second
condition is met, then do the sum up...

quot;Mark Lincolnquot; wrote:

gt; You can use SUMPRODUCT like this:
gt;
gt; =SUMPRODUCT(--(A3:A10=quot;MAquot;),--(B3:B10gt;5000),F3:F10)
gt;
gt; Your state abbreviations are in A3:A10, your numbers to test on are in
gt; B3:B10, and the numbers to sum are in F3:F10 (column 6). Change to
gt; suit your circumstance.
gt;
gt;

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=quot;MAquot;),F3:F10) SUMPRODUCT(--(B3:B10gt;5000),F3:F10)-SUMPRODUCT(--(A3:A10=quot;MAquot;),--(B3:B10gt;5000),F3:F10)

=SUMIF(A3:A10,quot;MAquot;,F3:F10) SUMIF(B3:B10,quot;gt;5000quot;,F3 :F10)-SUMPRODUCT(--(A3:A10=quot;MAquot;),--(B3:B10gt;5000),F3:F10)Hi!

Try this (using Marks example):

=SUMPRODUCT(--((A3:A10=quot;MAquot;) (B3:B10gt;5000)gt;0),F3:F10)

Biff

quot;xwenxquot; gt; wrote in message
...
gt; Thank you, Mark.
gt; What if the condition is an quot;orquot; (not and). Either the first or the second
gt; condition is met, then do the sum up...
gt;
gt; quot;Mark Lincolnquot; wrote:
gt;
gt;gt; You can use SUMPRODUCT like this:
gt;gt;
gt;gt; =SUMPRODUCT(--(A3:A10=quot;MAquot;),--(B3:B10gt;5000),F3:F10)
gt;gt;
gt;gt; Your state abbreviations are in A3:A10, your numbers to test on are in
gt;gt; B3:B10, and the numbers to sum are in F3:F10 (column 6). Change to
gt;gt; suit your circumstance.
gt;gt;
gt;gt;
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. :-)This is a bit shorter:

=SUMPRODUCT(--((A3:A10=quot;MAquot;) (B3:B10gt;5000)gt;0),F3:F10)In article .comgt;,
quot;Mark Lincolnquot; gt; wrote:

gt; That complicates matters some. We have to sum the rows in which either
gt; criterion is met and subtract the rows where both are met (otherwise
gt; the latter cases would be counted twice). Either of these work:
gt;
gt; =SUMPRODUCT(--(A3:A10=quot;MAquot;),F3:F10) SUMPRODUCT(--(B3:B10gt;5000),F3:F10)-SUMPROD
gt; UCT(--(A3:A10=quot;MAquot;),--(B3:B10gt;5000),F3:F10)
gt;
gt; =SUMIF(A3:A10,quot;MAquot;,F3:F10) SUMIF(B3:B10,quot;gt;5000quot;,F3 :F10)-SUMPRODUCT(--(A3:A10=quot;
gt; MAquot;),--(B3:B10gt;5000),F3:F10)

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

In article gt;,
JE McGimpsey gt; wrote:

gt; This is a bit shorter:
gt;
gt; =SUMPRODUCT(--((A3:A10=quot;MAquot;) (B3:B10gt;5000)gt;0),F3:F10)
gt;