# Dumb Excel Questions **URL:** \ **Category:** Software/Technology\ **Created:** [February 3, 2022, 6:03pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148 "2022-02-03T18:03:06Z")\ **Posts on this page:** 20\ **Page:** 4
**Author:** ![dr\_t\_non-fan](https://community.goactuary.com/letter_avatar_proxy/v4/letter/d/a8b319/32.png) [@dr\_t\_non-fan](https://community.goactuary.com/u/dr_t_non-fan)\ **Post date:** [November 17, 2022, 8:10pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/61 "2022-11-17T20:10:04Z")
Make a formula (results are 1 or 0) in Column D based on the contents of Column C. SUMPRODUCT ((…)\*(…)) is easier to read, though. ---
**Author:** ![AlexioXela](https://community.goactuary.com/user_avatar/community.goactuary.com/alexioxela/32/550_2.png) [@AlexioXela](https://community.goactuary.com/u/AlexioXela)\ **Post date:** [November 17, 2022, 9:13pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/62 "2022-11-17T21:13:28Z")
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. ---
**Author:** ![1695814](https://community.goactuary.com/user_avatar/community.goactuary.com/1695814/32/38506_2.png) [@1695814](https://community.goactuary.com/u/1695814)\ **Post date:** [November 17, 2022, 10:11pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/63 "2022-11-17T22:11:34Z")
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! ---
**Author:** ![SredniVashtar](https://community.goactuary.com/user_avatar/community.goactuary.com/srednivashtar/32/2894_2.png) [@SredniVashtar](https://community.goactuary.com/u/SredniVashtar)\ **Post date:** [November 19, 2022, 7:22pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/64 "2022-11-19T19:22:49Z")
> [@1695814](#): > > =SUMIFS(A1:A10,B1:B10,“ABC”,C1:C10,“XYZ”) > +SUMIFS(A1:A10,B1:B10,“ABC”,C1:C10,“123”) > > …maybe some kind of array fn. 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. ---
**Author:** ![DeepPurple](https://community.goactuary.com/user_avatar/community.goactuary.com/deeppurple/32/1551_2.png) [@DeepPurple](https://community.goactuary.com/u/DeepPurple)\ **Post date:** [November 22, 2022, 3:39pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/65 "2022-11-22T15:39:55Z")
> [@1695814](#): > > =SUMIFS(A1:A10,B1:B10,“ABC”,C1:C10,OR(“XYZ”,“123”)) =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. ---
**Author:** ![SredniVashtar](https://community.goactuary.com/user_avatar/community.goactuary.com/srednivashtar/32/2894_2.png) [@SredniVashtar](https://community.goactuary.com/u/SredniVashtar)\ **Post date:** [November 22, 2022, 4:08pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/66 "2022-11-22T16:08:44Z")
What happens when they’re both true though? Does it return a 2? ---
**Author:** ![DeepPurple](https://community.goactuary.com/user_avatar/community.goactuary.com/deeppurple/32/1551_2.png) [@DeepPurple](https://community.goactuary.com/u/DeepPurple)\ **Post date:** [November 22, 2022, 4:09pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/67 "2022-11-22T16:09:32Z")
How could one cell possibly be equal to “XYZ” and equal to “123” at the same time? ---
**Author:** ![BruteForce](https://community.goactuary.com/user_avatar/community.goactuary.com/bruteforce/32/175_2.png) [@BruteForce](https://community.goactuary.com/u/BruteForce)\ **Post date:** [November 22, 2022, 4:38pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/68 "2022-11-22T16:38:15Z")
I think the plus sign is supposed to equal OR, so it’s if column C equals “XYZ” or “123” ---
**Author:** ![1695814](https://community.goactuary.com/user_avatar/community.goactuary.com/1695814/32/38506_2.png) [@1695814](https://community.goactuary.com/u/1695814)\ **Post date:** [November 22, 2022, 4:41pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/69 "2022-11-22T16:41:25Z")
> [@DeepPurple](#): > > How could one cell possibly be equal to “XYZ” and equal to “123” at the same time? I forgot to mention that I’m using the new version of Quantum Excel. It’s still in Beta. My cat loves it. ---
**Author:** ![DeepPurple](https://community.goactuary.com/user_avatar/community.goactuary.com/deeppurple/32/1551_2.png) [@DeepPurple](https://community.goactuary.com/u/DeepPurple)\ **Post date:** [November 22, 2022, 4:58pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/70 "2022-11-22T16:58:36Z")
> [@BruteForce](#): > > I think the plus sign is supposed to equal OR, so it’s if column C equals “XYZ” or “123” 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 ---
**Author:** ![SredniVashtar](https://community.goactuary.com/user_avatar/community.goactuary.com/srednivashtar/32/2894_2.png) [@SredniVashtar](https://community.goactuary.com/u/SredniVashtar)\ **Post date:** [November 22, 2022, 6:34pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/71 "2022-11-22T18:34:48Z")
> [@DeepPurple](#): > > How could one cell possibly be equal to “XYZ” and equal to “123” at the same time? 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. ---
**Author:** ![DeepPurple](https://community.goactuary.com/user_avatar/community.goactuary.com/deeppurple/32/1551_2.png) [@DeepPurple](https://community.goactuary.com/u/DeepPurple)\ **Post date:** [November 22, 2022, 7:18pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/72 "2022-11-22T19:18:42Z")
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) ---
**Author:** ![procrastinator](https://community.goactuary.com/letter_avatar_proxy/v4/letter/p/45deac/32.png) [@procrastinator](https://community.goactuary.com/u/procrastinator)\ **Post date:** [November 22, 2022, 7:24pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/73 "2022-11-22T19:24:04Z")
If X = 41, Y = 1, and Z = 3? ---
**Author:** ![1695814](https://community.goactuary.com/user_avatar/community.goactuary.com/1695814/32/38506_2.png) [@1695814](https://community.goactuary.com/u/1695814)\ **Post date:** [November 22, 2022, 11:27pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/74 "2022-11-22T23:27:26Z")
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? ---
**Author:** ![DeepPurple](https://community.goactuary.com/user_avatar/community.goactuary.com/deeppurple/32/1551_2.png) [@DeepPurple](https://community.goactuary.com/u/DeepPurple)\ **Post date:** [November 22, 2022, 11:40pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/75 "2022-11-22T23:40:03Z")
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. ---
**Author:** ![1695814](https://community.goactuary.com/user_avatar/community.goactuary.com/1695814/32/38506_2.png) [@1695814](https://community.goactuary.com/u/1695814)\ **Post date:** [November 23, 2022, 2:08am UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/76 "2022-11-23T02:08:28Z")
> [@DeepPurple](#): > > 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? 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. ---
**Author:** ![SredniVashtar](https://community.goactuary.com/user_avatar/community.goactuary.com/srednivashtar/32/2894_2.png) [@SredniVashtar](https://community.goactuary.com/u/SredniVashtar)\ **Post date:** [November 25, 2022, 1:53am UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/77 "2022-11-25T01:53:09Z")
> [@DeepPurple](#): > > If there’s no nudity, no slander, and no vulgarity, I’ll allow it. If you manage to actually slip these things into a spreadsheet, I’ll commend you. ---
**Author:** ![Maphistos\_Sidekick](https://community.goactuary.com/user_avatar/community.goactuary.com/maphistos_sidekick/32/84973_2.png) [@Maphistos\_Sidekick](https://community.goactuary.com/u/Maphistos_Sidekick)\ **Post date:** [November 25, 2022, 4:30am UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/79 "2022-11-25T04:30:39Z")
You haven’t seen some of my VBA code. ---
**Author:** ![SredniVashtar](https://community.goactuary.com/user_avatar/community.goactuary.com/srednivashtar/32/2894_2.png) [@SredniVashtar](https://community.goactuary.com/u/SredniVashtar)\ **Post date:** [November 25, 2022, 2:54pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/80 "2022-11-25T14:54:36Z")
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. ---
**Author:** ![twig93](https://community.goactuary.com/user_avatar/community.goactuary.com/twig93/32/527_2.png) [@twig93](https://community.goactuary.com/u/twig93)\ **Post date:** [November 25, 2022, 3:07pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/81 "2022-11-25T15:07:16Z")
Would probably get net-nannied and not have the intended affect. Could still get you fired, I assume. [Previous page](https://community.goactuary.com/t/dumb-excel-questions/5148.md?page=3) [Next page](https://community.goactuary.com/t/dumb-excel-questions/5148.md?page=5)