Counting Cells with Specific Strings Row by Row (IF AND Statement?)-CodePudding

Summary Goal: count cells that contain specific strings in columns A AND B checking each pair of rows in the columns.

What I have So Far: =IF(AND(B3="GA", C3="Grocery"), COUNTIF(C3, "Grocery"),0)
What I Need: how to specify continuing this process for each pair of rows in columns A and B.
Context: counting business in different states that have been assigned specific categories. For example, how many businesses in Georgia ("GA") are categorized as "Grocery"?

Details

I am creating a function with the goal of checking cells in columns A and B in the same row to match a specific string for each, adding 1 to the count if they both match, then checking the next row in columns A and B. How can the function be written to continue checking all of columns A AND B row by row? Thank you!

CodePudding user response：

Try something like-

=BYROW(FILTER(B3:C,C3:C<>""),LAMBDA(x,IF(AND(INDEX(x,1,1)="GA",INDEX(x,1,2)="Grocery"),COUNTIFS(C3:C,"Grocery"),"")))

Or try-

=BYROW(B3:C,LAMBDA(x,IF(AND(INDEX(x,1,1)="GA",INDEX(x,1,1)<>"",INDEX(x,1,2)="Grocery"),COUNTIFS(C3:C,"Grocery",B3:B,"GA",INDEX(ROW(C3:C)),"<="&ROW(x)),"")))

CodePudding user response：

try:

=INDEX(IF((B3:B="GA")*(C3:C="Grocery"), 
 COUNTIFS(C3:C, "Grocery", ROW(C3:C), "<="&ROW(C3:C)), 0))