Count 1st, 2nd occurrence in visible cells

[georginalo]

Hello, how to I count the 1st, 2nd, 3rd... occurrence in visible (filtered) cells?

I used =COUNTIF($A$7:$A7, $A7) with all cells, but how to convert to filtered cells?

The date I want to count is people's name, ie not number.

Thank you!

 

[georginalo]

The date I want to count is people's name, ie not number.

Sorry, I meant DATA, not DATE.

 

[Aladin Akyurek]

Are you after this?

=SUMPRODUCT(SUBTOTAL(2,OFFSET($A$7,ROW($A$7:$A$200)-ROW($A$7),0)),--($A$7:$A$200=$A7))

 

[georginalo]

Are you after this?

=SUMPRODUCT(SUBTOTAL(2,OFFSET($A$7,ROW($A$7:$A$200)-ROW($A$7),0)),--($A$7:$A$200=$A7))

Thanks! But I have tried this and all results is 0, doesn't work.

 

[Aladin Akyurek]

Thanks! But I have tried this and all results is 0, doesn't work.

2 for numeric data, try 3...

=SUMPRODUCT(SUBTOTAL(3,OFFSET($A$7,ROW($A$7:$A$200)-ROW($A$7),0)),--($A$7:$A$200=$A7))​

 

[georginalo]

2 for numeric data, try 3...

=SUMPRODUCT(SUBTOTAL(3,OFFSET($A$7,ROW($A$7:$A$200)-ROW($A$7),0)),--($A$7:$A$200=$A7))​

Thank you!
This shows the number of occurrences, but what if I want to show the 1st, 2nd, 3rd, 4th.... occurrences?

 

[georginalo]

I mean something like below

Apple 1
Banana 1
Apple 2
Cherry 1
Banana 2
Apple 3
.
.
.

 

[Marcelo Branco]

Not sure i understand what you want. See if this example helps.

Before filter

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Fruit​

[/TD]
[TD]

Sales​

[/TD]
[TD]

Formula​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Apple​

[/TD]
[TD]

80​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Banana​

[/TD]
[TD]

100​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Apple​

[/TD]
[TD]

120​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Cherry​

[/TD]
[TD]

60​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Banana​

[/TD]
[TD]

70​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Apple​

[/TD]
[TD]

110​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Apple​

[/TD]
[TD]

40​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

Banana​

[/TD]
[TD]

80​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

Apple​

[/TD]
[TD]

120​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

Cherry​

[/TD]
[TD]

110​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

Banana​

[/TD]
[TD]

130​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

Apple​

[/TD]
[TD]

120​

[/TD]
[TD]

6​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in C2 copied down
=SUMPRODUCT(SUBTOTAL(3,OFFSET(A$2:A2,ROW(A$2:A2)-ROW(A$2),0,1)),--(A$2:A2=A2))

Apply filter in Sales column
Criteria: Sales > = 100 (for example)

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Fruit​

[/TD]
[TD]

Sales​

[/TD]
[TD]

Formula
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Banana​

[/TD]
[TD]

100​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Apple​

[/TD]
[TD]

120​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Apple​

[/TD]
[TD]

110​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

Apple​

[/TD]
[TD]

120​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

Cherry​

[/TD]
[TD]

110​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

Banana​

[/TD]
[TD]

130​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

Apple​

[/TD]
[TD]

120​

[/TD]
[TD]

4
​

[/TD]
[/TR]
</tbody>[/TABLE]

M.

 

[georginalo]

Yes, this is exactly what I wanted, thank you very very much!!!!!!