Search code examples
google-sheetsfiltersumsumifstextjoin

How to exclude multiple strings from SUMIFS

I have this formula here:

=SUMIFS(SalesData!$N:$N, SalesData!$B:$B, "FirstName LastName", SalesData!$D:$D, "<>"&A1:M1)

But the last part "<>"&A1:M1 doesn't work correctly. If I set it to a specific cell, e.g. "<>"&F1, that works fine. Is there a way to select the entire range of A1:M1?

Solution

  • try:

    =SUM(FILTER(SalesData!N:N, SalesData!B:B="FirstName LastName", 
 NOT(REGEXMATCH(SalesData!D:D&"", TEXTJOIN("|", 1, A1:M1)))))