CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaspreadsheet
Irfan ABD
Irfan ABD

Reputation: 11

Google Sheet Countifs with Multiple Criteria

I'm planning to create a dynamic cell which can count total items or only certain items. I used the following formula to get the total items. But this formula doesn't work when I filter by item.

=ARRAYFORMULA(SUM(COUNTIFS(A:A;{“D1”;{“APPLE”;”ORANGE”;”POMEGRANATES”}};B:B;”1/6/2022″)))

*D1 = Dropdown for Apple/Orange/Pineapple/Pomegranates

The result I expect is:

If I select Apple then the only value that appears is Apple (4/10).

And If I don’t select Apple then the values that appear are all values (10/10)

I would be very grateful if you could tell me where the error is and provide a solution.

Demo Sheets

Upvotes: 1

Views: 219

Answers (2)

tomf
tomf

Reputation: 535

Try this out

=QUERY(
  {A:B};
  "select Count(Col1)
   where
    Col1 matches '"&IF(ISBLANK(D1);".*";D1)&"' and
    Col2 = date '"&TEXT(DATE(2022;6;1);"yyyy-mm-dd")&"'
   label Count(Col1) ''")

If D1 is blank, it'll return everything (.* / the wildcard) -- otherwise it'll pull the word in D1.

If you wanted to use a cell reference for the date, you can replace DATE(2022;6;1) with that cell reference.

Upvotes: 2

JPV
JPV

Reputation: 27242

Try

=sumproduct(regexmatch(A:A; IF(LEN(D1); D1; "Apple|Orange|Pomegranates|Pineapple")); B:B= date(2022; 6; 1))

and see if that works?

Note that regexmatch is case-sensitive.

Upvotes: 1

Related Questions