I'm trying to see if there is any way to count in Google Sheets how many times conditions in other columns are met. Please see image below. In the example, there are 3 conditions (Car, Color, Status) and they will all be counted as the first case if no conditions are repeated in previous rows. If the same conditions are met in a previous row, the count will go up in one. In the example, only only in the case of Mercedes, red, new is repeated twice, so in the second time it happens, the result will show 2 in the second time.

Image and desired outcome


Use countifs() with three conditions, like this:

  A$2:A2, A2, 
  B$2:B2, B2, 
  C$2:C2, C2 

Assuming that the header "Car" shown in your image is in A1 and that your formula and results would be in D:D, clear Column D and place the following formula in D1:

=ArrayFormula({"Header of Choice";IF(A2:A="",,COUNTIFS(A2:A&B2:B&C2:C,A2:A&B2:B&C2:C,ROW(A2:A),"<="&ROW(A2:A)))})

This formula will produce the header (which you can change inside the formula as you like) and all results for the column.

COUNTIFS will count for concatenated matches of everything in Columns A, B and C, counting only as far as the current row.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.