Countif but with criteria ("Y")

[ellison]

Hi, we use a really helpful couple of counts to show us the "nth" occurrence and the overall count of number of occurrences of a string.
Cell C2:

=COUNTIF(B$2:B2,B2)

Cell D2:

=COUNTIF(B:B,B2)

We are wondering if something similar could be done... BUT also take into account whether or not to use the string in the counts

i.e. checking that the criteria on that row is a "y" before including it in the counts

For example below: "Charlie" appears on 3 rows. But only 2 of those 2 rows have "y" as the criteria. So is there maybe a way to calculate the countifs to reflect that?

Row	Header-1	nth [no criteria]	nth overall [no criteria]	Criteria	nth [where criteria is "Y"]	overall [where criteria is "Y"]
2	alpha	1	1	y	1	1
3	beta	1	3	y	1	2
4	beta	2	3
5	beta	3	3	y	2	2
6	charlie	1	3
7	charlie	2	3	n
8	charlie	3	3	y	1	1
9		0	0
10	delta	1	1	y	1	1

Thanks for taking a peek...

 

[RoryA]

Use COUNTIFS:

=COUNTIFS(B$2:B2,B2,E$2:E2,"Y")

 

[Fluff]

In future please use the mini-sheet option as it includes any formulae & also shows us which columns/rows are being used.

Try

=IF(E2="y",COUNTIFS(B$2:B2,B2,E$2:E2,"y"),"")

 

[ellison]

Huge thanks for your help, that works beautifully!

And apologies - I will use the mini-sheet option in future, sorry

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[ellison]

pleasure!