CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
user13006535
user13006535

Reputation:

How to count occurrence in previous rows based on two columns value

I'm trying to count the number of occurrence in previous rows based on two conditional values using Google Sheet.

Let say this is my table :

Row A Row B Row C Row D
1 John Smith
2 Marty Butler
3 John Herbert
4 John Smith
5 Philip Rand
6 John Smith
7 Marty Butler

Is there a formula that exist that can count those occurrences. The idea is that when I log a new name, if Row B and C already exist it increase the value in Row D by 1 so I would know that it is the nth entry under that name. In my example, Row D would looks like this:

Row A Row B Row C Row D
1 John Smith 1
2 Marty Butler 1
3 John Herbert 1
4 John Smith 2
5 Philip Rand 1
6 John Smith 3
7 Marty Butler 2

Upvotes: 0

Views: 687

Answers (1)

Erik Tyler
Erik Tyler

Reputation: 9345

Delete everything in Column D (including the header) and place the following in D1:

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

The "Header" text can be edited as you like.

The COUNTIFS reads, in plain English, "Count how many times this first and last name combination has occurred within only the rows up to the current row."

Upvotes: 1

Related Questions