I need to sum the values with non-continuous sum ranges and non-continuous multiple criteria. If data in BOTH columns B and C says No, then values should NOT be summed. See attached. The total in my example should be 24.
I need to sum the values with non-continuous sum ranges and non-continuous multiple criteria. If data in BOTH columns B and C says No, then values should NOT be summed. See attached. The total in my example should be 24.
Try this:
=SUMIFS(A2:A17,B2:B17,"<>No",C2:C17,"<>No")
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
No, it's only sums up "Yes" in both columns, it has to sum up if the following variations are true
1. Yes, Yes
2. Yes, No
3. No, Yes
This yields the desired result of 24:
=SUMPRODUCT(A2:A17, --(--(B2:B17="yes")+(--(C2:C17="yes")) >= 1))
Geoff
Did I help significantly? If you wish, click on * Add Reputation to say thanks.
If your problem has been resolved please select ?Solved? from the Thread Tools menu
Can you explain it, specifically -- and >=1. I understand + acts as an OR operator. Is it correct?
I'm sure there's a more elegant way but this was the first thing I came up with.
In this case the "+" is actually a normal arithmetic "+".
- B2:B17="yes" returns true or false. The --() around B2:B17="yes" coerces TRUE or FALSE to 1 or zero.
- The same logic applies to C2:C17="yes".
- So --(B2:B17="yes")+(--(C2:C17="yes")) = 2 if both B2 and C2 are "yes", 1 if (B2="yes" and C2="no") or (B2="no" and C2="yes") and 0 if both B2 and C2 are "no".
- So the only case we don't want to count is when B2 and C2 are both "no" and the above expression = 0 Hence the >=1 test.
- The outer wrapping --() is because sumproduct() won't automatically convert TRUE/FALSE to 1/0.
- Sumproduct does the above calculation row by row and we end up with an array of 1's and 0's which, finally, are multiplied by the array of values in A2:A17 and then summed to give the final result.
Hopefully this clarifies what is happening rather than confuses!
The best way to see what is happening during a formula evaluation is Excel's "Formulas > Evaluate-formula feature. It will show you step by step how Excel evaluates a formula.
Here's a somewhat more straightforward option!
=SUMPRODUCT(A2:A17, --NOT((B2:B17="no")*(C2:C17="no")))
A B C D E 1Values Data1 Data2 2 2Yes Yes 24 3 1Yes No 4 4No No 5 4No Yes 6 4No No 7 2Yes Yes 8Other info1 9 3No No 10 1No Yes 11 5No No 12 2Yes Yes 13Other info2 14 1No No 15 4No Yes 16 2No No 17 8Yes Yes
E2=SUMPRODUCT=(--(ISERROR(SEARCH($B$4&C4,$B$2:$B$17&C2:C17))),A2:A17)
@CARACALLA You have a typo in the above formula. A superfluous "=" after SUMPRODUCT
A B C D E F 1Values Data1 Data2 2 2Yes Yes 24 3 1Yes No 4 4No No 5 4No Yes 6 4No No 7 2Yes Yes 8Other info1 9 3No No 10 1No Yes 11 5No No 12 2Yes Yes 13Other info2 14 1No No 15 4No Yes 16 2No No 17 8Yes Yes
F2=SUMPRODUCT(--(ISERROR(SEARCH($B$4&C4,B2:B17&C2:C17))),A2:A17)
If the other info is not numeric, the formula will error as it can not sum the values, if this is the case
=SUM(A2:A17)-SUMIFS(A2:A17,B2:B17,"No",C2:C17,"No") might be the answer
but you are not telling us the problems with the previous solutions
Another option
=SUMPRODUCT(SIGN((B2:B17="Yes")+(C2:C17="Yes")),A2:A17)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks