How can I sum a column if another is not equal to multiple criteria?

solved

I'm trying to sum column B if column A is not equal to "Text1" or "Text2" or "Text3".

I cant seem to get a sumif to work with or, it's driving me crazy lol

Posted Apr 27, 2021, 2:36 PM UTC

14
mh_mike2192•

Hmm, you might be forced to do a full sum and then subtract the others. Something like this:

=SUMIF($A$2:$A$8,"<>",$B$2:$B$8)
-SUMPRODUCT(($B$2:$B$8)*(($A$2:$A$8="this")+($A$2:$A$8="that")+($A$2:$A$8="other")))

That SUMIF grabs the sum of B2 thru B8 where A2 thru A8 is not-blank. Then it subtracts the SUMPRODUCT result which is grabbing a total of everything that is equal-to our trigger words (of "this", "that" & "other" in the example).

Or you could shorten it slightly with this:

=SUMIF($A$2:$A$8,"<>",$B$2:$B$8)
-SUMPRODUCT(($B$2:$B$8)*($A$2:$A$8={"this","that","other"}))
2
Fapper_KeeperOP•

Solution verified

Thanks! I didn't end up using this but it was helpful nonetheless!

2
Clippy_Office_AsstMOD•

You have awarded 1 point to mh_mike

^I ^am ^a ^bot, ^please ^contact ^the ^mods ^with ^any ^questions.

1
AutoModeratorMOD•

u/Fapper_Keeper - Your post was submitted successfully.

Please read these reminders and edit to fix your post where necessary:

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1