Search code examples
excelexcel-formulasumifs

Combine two SUMIFS statements?

How do I combine the following two sumifs statement?

=SUMIFS($I$50:$I$69,$G$50:$G$69,"Non-*")+SUMIFS($I$50:$I$69,$G$50:$G$69,P16)
Solution

  • Since you are limiting the ranges already, SUMPRODUCT may look a bit better but it is not going to improve calculation load.

    =SUMPRODUCT($I$50:$I$69, SIGN(($G$50:$G$69=P16)+(LEFT($G$50:$G$69, 4)="non-")))