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:

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

CodePudding user response：

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

1. uses BYROW() to iterate VARIANTS column and...
2. use QUERY() to filter ITEMS column for matches according to VARIANT and... FINDSIZE as criteria,
3. 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))))