Make a formula (results are 1 or 0) in Column D based on the contents of Column C.
SUMPRODUCT ((…)*(…)) is easier to read, though.
2 Likes
If you don’t want to make a column D and you don’t mind dealing with Booleans, you can use =SUM(IF((B1:B10=“ABC”)*((C1:C10=“XYZ”)+(C1:C10=“123”)),A1:A10,0)). You can use “+” for “OR” or * for “AND”. This works even if not input as an array function.
Unfortunately the OR and AND functions don’t seem to work well with arrays, so the perhaps more intuitive =SUM(IF(AND(B1:B10=“ABC”,OR(C1:C10=“XYZ”,C1:C10=“123”)),A1:A10,0)) yields incorrect results.
1 Like
I’ve definitely used * for AND and half heartedly considered using + for OR but then figured, “Naw, that’ll never work.” I will reconsider. Thanks!
This looks good to me, unless you have an overlapping “or”, in which case God have mercy.
I also really like wildcards with my sumifs.
=sumproduct(a1:a10,–(b1:b10=“ABC”),–(c1:c10=“XYZ”) + --(C1:c10=“123”))
This should work. The boolean expressions return "True"s and "False"s
You can add 0 to True or false to get 1 or 0. An Equivalent is minus minus, which is what I used here. In other words, putting the minus minus in front of the 10 boolean Trues or falses, you have turned the T/F into 1’a and 0’s. Make sense? DTNF was on the right track, but the minus minus trick allows you to do the work in the formula instead of adding a helper column.
I have always preferred sumproduct to sumif(s) or array functions. Sumproduct is more like the APL garbleygoop that I used to program.
1 Like
What happens when they’re both true though? Does it return a 2?
How could one cell possibly be equal to “XYZ” and equal to “123” at the same time?
I think the plus sign is supposed to equal OR, so it’s if column C equals “XYZ” or “123”
I forgot to mention that I’m using the new version of Quantum Excel. It’s still in Beta. My cat loves it.
3 Likes
Yes + is the boolean OR
0+0 = 0
1+0 = 1
0+1 = 1
1+1 =1
And * is the boolean AND
0 * 0 = 0
1 * 0 = 0
0 * 1 = 0
1 * 1 =1
Just curious. I mean, to me, the confusing part of 1695814’s question is when the OR isn’t exclusive but obviously that’s not what most sumifs do.
=SUMPRODUCT(A1:A10,–(B1:B10=“ABC”),–(C1:C10>1) + --(C1:C10>5))
This for example, will return 2s.
Yes, I see what you have done there. But the OR is superfluous in your example. If a number is greater than 5 is is of course greater than 1 and therefore one of the --(c1:c10)>X is unnecessary.
If you are going to use my trick here, just make sure that there is no overlap in the conditions that can cause a 1 {true} in the vectors that you generate.
If you had to, and I don’t recommend this, but you could do this:
Instead of: –(C1:C10>1) + --(C1:C10>5)
You could write it as --((–(C1:C10>1) + --(C1:C10>5))>=1)
This would cap any 2 that you generate at 1. See that?
I still think that the intent of the original question by OP was to identify mutually exclusive options.
Incidentally, any of these will convert a list of Trues and Falses to 1’s and 0’s, as they are all mathematical expresses that are essentially math nuetral:
- – (C1:C10>5) <---- there are 2 minuses here but the post makes it look like a single
- 0 + (C1:C10>5)
- 1 * (C1:C10>5)
If X = 41, Y = 1, and Z = 3?
New topic: should I leave cheeky comments out of my Excel documentation?
E.g., I have a year-field, which, because the accountants use two digit years, simply subtracts 2000 from the valuation year field to get the two digit year. I put a note in there saying “this will no longer work in the year 2100…also, if you’re still using this in 2100 you’ve got serious issues…also², 2100 is not a leap year”
(Actually, what I did was =VALUE(TEXT(VALDATE,”yy”)) where VALDATE is a named range with, of all things, the valuation date.)
I try to be professional, but, sometimes, I just can’t help myself.
Your thoughts?
If there’s no nudity, no slander, and no vulgarity, I’ll allow it. We have to entertain ourselves once in a while. The rule is, would you feel bad about telling the whole company up to the president what you wrote down?
And your example does not even qualify as cheeky in my book.
And = mod(year,100) will work better than =(year -2000) because it WILL work after 2100.
1 Like
Well, the problem is that some people have that corn cob stuck so far up their ass that they really don’t want to be bothered with a bit of humor.
I appreciate your take on it & wish everyone else was like DeepPurple.
It does remind me of some commentary left in some code by a (beloved) programmer. It was something along the lines of “I’ve inserted a bitflange procedure to subvercate the error locus and that should finally get this damn thing to run.” I got a good chuckle out of that…primarily because I wasn’t expecting it.
If you manage to actually slip these things into a spreadsheet, I’ll commend you.
You haven’t seen some of my VBA code.
1 Like
A great way to get fired would be to set up an important PowerPoint with vba to open browsers that jump to pornhub. Simple and hilarious… Maybe litigious.
Would probably get net-nannied and not have the intended affect.
Could still get you fired, I assume.