Forum Discussion

clich216's avatar
clich216
Copper Contributor
Mar 27, 2023

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

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.

excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Apr 01, 2023

    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.

      

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Mar 28, 2023

    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!

Resources