Google Sheet Countifs with Multiple Criteria-CodePudding

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).

Apple = 4
Orange = 3
Pineapple = 1
Pomegranates = 2

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

All Fruit = 10

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

Demo Sheets

CodePudding user response：

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.

CodePudding user response：

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.