Range of Cells as Criteria for COUNTIFS Function in Google Sheets

Question

I have a list that contains multiple items. However, each item has different variants.

I want to sum all occurrences of each item, regardless of the variant.

I am using the COUNTIFS function in Google Sheets but for the criteria, I want to input a range that is an array of strings.

=countifs(!A:A,("B:B"),!C:C,"small")

Where column B includes a list of different variant names and column C is sizing.

For example:

A	B	C
apples	apples	small
apples	applez	small
applez	applees	small
appleees		small
oranges		small

In this case I would want the result to = 4 because there were four total instances in column A where the criteria was met (using any string/row in column B) and since all sizes were small.

I was able to get the result I wanted using this formula however it is extremely cumbersome as there are many variants and they are constantly updated/changed concurrently in column B:

=countifs(A:A,"item variant 1",C:C,"small")
+countifs(A:A,"item variant 2",C:C,"small")
+countifs(A:A,"item variant 3",C:C,"small")
+countifs(A:A,"item variant 4",C:C,"small")
+countifs(A:A,"item variant 5",C:C,"small")

Seeking any improvement at all from there, I tried listing the variants within a range itself (making sure to use a semicolon for Google Sheets based on this answer) and couldn't get that to work either:

=countifs(A:A,{"item variant 1";"item variant 2";"item variant 3";"item variant 4";"item variant 5"},C:C,"small")

In the above case, it only counts instances of first variant mentioned in the range (in this case item variant 1).

not sure if I understand what you are trying to do, are you trying to count how many different inputs are in Column A according to a given criteria in Column C? you didn't even mention why Column B exists and the formula you say is working didn't take Column B in consideration... — Ping, Dec 11 '22 at 03:54

rockinfreakshow · Answer 1 · 2022-12-10T09:00:31.637

0

while you said the expected output is 4, I can only see 2 unique items matching for a count of 3(apples, applez)
& orange has zero match & appleees not a match to applees

formula:

=COUNTA(IFERROR(filter(A:A,(C:C="small")*(REGEXMATCH(A:A,TEXTJOIN("|",1,B2:B))))))

Alternate formula:

=COUNTA(ARRAYFORMULA(IF(LEN(A2:A)*(C2:C="small"),IFERROR(vlookup(A2:A6,B:B,1,)),)))

edited Dec 10 '22 at 09:00

answered Dec 10 '22 at 08:51

rockinfreakshow

15,077
3
12
19

"while you said the expected output is 4, I can only see 2 unique items matching for a count of 3" -- In column A there are a total of 4 instances of words listed in column B. – Heidi B. Dec 10 '22 at 09:27
'apples' 2 instances are matching and 'applez' 1 instance is matching. can you help me out with the expected 4th instance? – rockinfreakshow Dec 10 '22 at 09:44
@HeidiB. I agree that there are only three instances, A5 has 3 "e", and your options have 1 and 2 "e" – Martín Dec 10 '22 at 19:08

Ping · Answer 2 · 2022-12-11T08:11:27.407

Try this formula:

Assume that your data are always arranged as {ITEMS,VARIANTS,SIZES},

In this formula, you can adjust data range and search criteria according to the values in the last () (current values are $A:$C and "small"),

this formula...

uses BYROW() to iterate VARIANTS column and...
use QUERY() to filter ITEMS column for matches according to VARIANT and... FINDSIZE as criteria,
COUNT() the output of the filters by QUERY(), SUM() the RESULTS of all filters to get 3, since only apples and applez of the given VARIANTS has matches. (applees in VARIANTS has only 2 'e's while appleees in ITEMS has 3 'e's, makes it a non-match)

=ArrayFormula(
 LAMBDA(RANGE,FINDSIZE,
  LAMBDA(DATA,FINDSIZE,
   LAMBDA(ITEMS,VARIANTS,SIZES,
    LAMBDA(RESULTS,
     SUM(RESULTS)
    )(
     BYROW(VARIANTS,LAMBDA(VARIANT,
      LAMBDA(FILTER,
       SUM(IFNA(FILTER,0))
      )(QUERY({ITEMS,SIZES},"SELECT COUNT(Col1) WHERE Col1='"&VARIANT&"' AND Col2='"&FINDSIZE&"' LABEL COUNT(Col1)''",0))
     ))
    )
   )(INDEX(DATA,,1),QUERY({RANGE},"SELECT Col2 WHERE Col2 IS NOT NULL",0),LOWER(INDEX(DATA,,2)))
  )(QUERY({RANGE},"SELECT Col1,Col3 WHERE Col1 IS NOT NULL OR Col3 IS NOT NULL",0),LOWER(FINDSIZE))
 )($A:$C,"small")
)

If you don't concern the accessibility of the range and criteria, here is a shorter version:

=SUM(BYROW(B:B,LAMBDA(VARIANT,IFNA(IF(VARIANT="",0,QUERY({A:A,C:C},"SELECT COUNT(Col1) WHERE Col1='"&VARIANT&"' AND Col2='small' LABEL COUNT(Col1)''",0)),0))))

Range of Cells as Criteria for COUNTIFS Function in Google Sheets

2 Answers2