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

Dementedpenguin 0
Viewed 38

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"?
  • Example Google Sheet

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!
spreadsheet google-sheets
3 Answers

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)),"")))

enter image description here

Harun24hr0

As per sample sheet provided in comment, you can try this formula.

=COUNTIFS(C3:C,E16,B3:B,"GA")
Harun24hr0

try:

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