Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use more complex logical expression for sumif as creteria?
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.) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use more complex logical expression for sumif as creteria?
You can use SUMPRODUCT like this:
=SUMPRODUCT(--(A3:A10="MA"),--(B3:B105000),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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use more complex logical expression for sumif as creteri
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" wrote: You can use SUMPRODUCT like this: =SUMPRODUCT(--(A3:A10="MA"),--(B3:B105000),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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use more complex logical expression for sumif as creteri
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:B105000),F3:F10)-SUMPRODUCT(--(A3:A10="MA"),--(B3:B105000),F3:F10) =SUMIF(A3:A10,"MA",F3:F10)+SUMIF(B3:B10,"5000",F3 :F10)-SUMPRODUCT(--(A3:A10="MA"),--(B3:B105000),F3:F10) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use more complex logical expression for sumif as creteri
Hi!
Try this (using Marks example): =SUMPRODUCT(--((A3:A10="MA")+(B3:B105000)0),F3:F10) Biff "xwenx" wrote in message ... 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" wrote: You can use SUMPRODUCT like this: =SUMPRODUCT(--(A3:A10="MA"),--(B3:B105000),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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use more complex logical expression for sumif as creteri
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. :-) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use more complex logical expression for sumif as creteri
This is a bit shorter:
=SUMPRODUCT(--((A3:A10="MA")+(B3:B105000)0),F3:F10) In article .com, "Mark Lincoln" wrote: 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:B105000),F3:F10)-SUMPROD UCT(--(A3:A10="MA"),--(B3:B105000),F3:F10) =SUMIF(A3:A10,"MA",F3:F10)+SUMIF(B3:B10,"5000",F3 :F10)-SUMPRODUCT(--(A3:A10=" MA"),--(B3:B105000),F3:F10) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use more complex logical expression for sumif as creteri
Oops, didn't see the identical solution in the other subthread...
In article , JE McGimpsey wrote: This is a bit shorter: =SUMPRODUCT(--((A3:A10="MA")+(B3:B105000)0),F3:F10) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you determine if a field is blank in a logical expression. | Excel Discussion (Misc queries) | |||
Can Excel represent formula in textural format with values substi. | Excel Worksheet Functions |