SUMIF with OR

[RJB78]

Hello -

I am trying to create a formula that will sum numbers from column I34:I5000 as long as B34:B5000 doesn't have the words "Unassociated" or "Unmanaged"

I have =SUMIF(B34:B5000,OR("<>Unassociated","<>Unmanaged"),I34:I5000) now, but it returns 0

Thank you for your help

 

[Joe4]

SUMIFS allows for multiple conditions. So try:

=SUMIFS(B34:B5000,I34:I5000,"<>Unassociated",I34:I5000,"<>Unmanaged")

See: https://www.techonthenet.com/excel/formulas/sumif.php

If using an old version of Excel that does not have SUMIFS, use:

=SUMPRODUCT(--(B34:B5000),--(I34:I5000<>"Unassociated"),--(I34:I5000<>"Unmanaged"))

 

[RJB78]

It says I entered to many arguments.

=SUMIF(range,criteria,[sum_range])

 

[Joe4]

SUMIFS, not SUMIF. They are different functions with different structures.
What version of Excel are you using? I believe SUMIFS was introduced with Excel 2007.
If you have an older version of Excel, then use the SUMPRODUCT solution I suggested instead.

 

[RJB78]

Thank you Joe. I am using the 2016 version. It returned a 0 for me again using the formula you gave above

 

[Joe4]

Are the values in column B formatted as numbers or text?
An easy way to tell is like this:
=ISNUMBER(B34)
If it returns False, then B34 is not a number, so summing it will not work until you convert it to a number.

Also, regarding your entries in column I, are the cells equal to EXACTLY "Unassociated" or "Unmanaged", or might there be a bunch of words in a single cell and you are just looking for the existence of these words within that string?

 

[RJB78]

Ah! We had the sum range and criteria range swapped. The criteria is in column B and the numbers are in column I. I made the necessary adjustments and it worked. Thank you