SUMIFS Formula with OR

[rsmeyerson]

Hi,

I am trying to create a SUMIFS formula that will work if a specific criteria is present in either of two cells (B35 OR B36). The following formula works when a criteria is present in a single cell but I'm having trouble adjusting this formula.

=SUMIFS(Sheet1!D:D,Sheet1!A:A,B35,Sheet1!B:B,"New")


Thanks for your help.

 

[Chrisdontm]

Let's assume that you want the word "Red" to be your criteria that must be present.

Then try this formula;

=IF(B35="Red",SUMIFS(Sheet1!D:D,Sheet1!A:A,B35,Sheet1!B:B,"New"),IF(B36="Red",SUMIFS(Sheet1!D:D,Sheet1!A:A,B36,Sheet1!B:B,"New"),""))

If neither one has the correct criteria, it will leave it blank...

 

[Aladin Akyurek]

=SUMPRODUCT(SUMIFS(Sheet1!D:D,Sheet1!A:A,B35:B36,Sheet1!B:B,"New"))

or, using definite ranges instead of whole columns...

=SUMPRODUCT(Sheet1!D2:D1000,--ISNUMBER(MATCH(Sheet1!A2:A1000,B35:B36,0)),--(Sheet1!D2:D1000="New"))

or, given your wording of OR...

=SUMIFS(Sheet1!D:D,Sheet1!A:A,IF(B35="",B36,B35),Sheet1!B:B,"New")

 

[rsmeyerson]

Alandin's SUMIFS formula works perfectly. Thanks for your help.

 

[Aladin Akyurek]

Alandin's SUMIFS formula works perfectly. Thanks for your help.

Glad that we could help.

 

[rsmeyerson]

=SUMPRODUCT(SUMIFS(Sheet1!D:D,Sheet1!A:A,B35:B36,Sheet1!B:B,"New"))

or, using definite ranges instead of whole columns...

=SUMPRODUCT(Sheet1!D2:D1000,--ISNUMBER(MATCH(Sheet1!A2:A1000,B35:B36,0)),--(Sheet1!D2:D1000="New"))

or, given your wording of OR...

=SUMIFS(Sheet1!D:D,Sheet1!A:A,IF(B35="",B36,B35),Sheet1!B:B,"New")

Since adding your SUMIFS formula above, my excel spreadsheet is lagging and running extremely slowly. I also added this formula to many of the sheets: =IF(ISNUMBER(LOOKUP(9.99999999999999E+307,MATCH(B171:B172,'[SheetName.xlsm]Sheet1'!A:A, 0))),"","Adj New:"). Any idea if these formulas might be causing this? Is there a way to speed it up? Thanks.

 

[Aladin Akyurek]

Since adding your SUMIFS formula above, my excel spreadsheet is lagging and running extremely slowly. I also added this formula to many of the sheets: =IF(ISNUMBER(LOOKUP(9.99999999999999E+307,MATCH(B171:B172,'[SheetName.xlsm]Sheet1'!A:A, 0))),"","Adj New:"). Any idea if these formulas might be causing this? Is there a way to speed it up? Thanks.

That's possible. Try to replace the hole column references with definite ones, that is, e.g., A:A >> A2:A2000 etc.