In Google Sheet, how do you use SUMIFS with OR logic matching a list of items?

Daniel L. 0
Viewed 34

In order to clarify the scenario, please check the example here: https://docs.google.com/spreadsheets/d/1E_xyPvkTIObG5JA4UfG1EQa74cFEc9QiznidJ4HSmEY/edit?usp=sharing

What I want to do here is to:

  1. filter by date (matching a certain date)
  2. filter by product id (matching a list of specified ids)
  3. sum up the total

I did try to find some ideas, though none of them worked, including the solution in the article below https://exceljet.net/formula/sumifs-with-multiple-criteria-and-or-logic

Really appriciate if someone could point me in the right direction, thanks!

sumifs google-sheets-formula google-sheets
2 Answers

use:

=QUERY({'raw data'!A2:C}, 
 "select Col1,sum(Col3) 
  where Col2 matches '"&TEXTJOIN("|", 1, 'ids to match'!A2:A)&"' 
  group by Col1 
  order by Col1 desc
  label sum(Col3)''")

enter image description here

player00

Sumifs is different than Sumif. You also have some text mixed with numbers. Your dates are text while Column b is numeric. Try this:

=Sumifs(C:C:,A:A,"2022-09-08",B:B,82267,B:B,82602) + 
 Sumifs(C:C:,A:A,"2022-09-08",B:B,82267,B:B,82602B:B,82604)

As Player() points out, if you have too many products to include, using sumifs would get pretty tedious and you ought to consider using a different function (i'd use filter). However since your question was Sumifs related, that's your answer.

To illustrate how this would work with something Filter, you could do this:

=sum(filter('raw data'!C:C,('raw data'!A:A=A2)*
  (isnumber(match('raw data'!B:B,'ids to match'!A:A,0)))))
pgSystemTester0
Related