Iterating through columns and counting if a cell in that row contains A, B, or C and summing

Copper Contributor

Hello, 

 

I have a large range of cells that each have a list associated with them, such as Bag, Box, Cart, Tray. Another column in the range of cells has months associated with the rows. My goal to count how many of each option there is for a given month and doing so over a range of cells. I have found similar ideas using BYROW() and COUNTIF(). COUNTIF() works as expected, but I can't get the BYROW() to work how I want. Below is an example of what I am trying to do.

 

Thanks for the help.

Screenshot 2023-03-27 131417.png

2 Replies

@clich216 

You can use the COUNTIFS function to count how many of each option there is for a given month. 

Here’s an example formula that counts how many times “Bag” appears in column A when the corresponding month in column B is “January”: =COUNTIFS(A:A,"Bag",B:B,"January").

As for BYROW(), it is used to apply a formula to each row of a range of cells.

You can use it with COUNTIF() to count how many times a value appears in each row of a range of cells. Here’s an example formula that counts how many times “Bag” appears in each row of a range of cells: =BYROW(A1:A10,LAMBDA(row,COUNTIF(row,"Bag"))).

 

I hope this helps!

@clich216 

=MAKEARRAY(COUNTA(B10:B13),
COUNTA(C9:E9),LAMBDA(r,c,SUM((E2:E7=INDEX(C9:E9,c))
*(A2:D7=INDEX(B10:B13,r)))))

An alternative could be MAKEARRAY.

makearray.JPG