Duplicate Value Counting

[canthony24]

Looking to solve this issue I'm trying to figure out. I need a formula to return the count of suppliers that have "Y" in their respective column.

Here is a small subset of data:

A1 = Vendor Name B1 = Minority Supplier
A2 = Goya B2 = Y
A3 = Goya B3 = Y
A4 = Monaco B4 = N
A5 = Shasta B5 = Y
A6 = Nogales B6 = Y

Now, these could have duplicate lines, like A2:A3. So A2:A50 could be Goya and B2:B50 could have all "Y". I want to return just the specific number of Vendors that have Y. In that subset, the answer would be 3, not 4. I want to avoid a pivot table if possible.

Thanks for the help if possible!

 

[Weazel]

maybe something like....

A8=SUM(IF(FREQUENCY(IF(B2:B6="y",MATCH(A2:A6,A2:A6,0)),ROW(A2:A6)-ROW(A2)+1),1)) Control Shift Enter

Row\Col	A​	B​
1​	Vendor Name	Minority Supplier
2​	Goya	Y
3​	Goya	Y
4​	Monaco	N
5​	Shasta	Y
6​	Nogales	Y
7​
8​	3​

<tbody>
</tbody>

 

[canthony24]

Perfect. How would in incorporate this if I want the formula to be in another sheet that reads the data? A8 is the cell of sheet I want it in, and the data lives in 'Source Data'

 

[Weazel]

something like...

=SUM(IF(FREQUENCY(IF('source data'!B2:B6="y",MATCH('source data'!A2:A6,'source data'!A2:A6,0)),ROW('source data'!A2:A6)-ROW('source data'!A2)+1),1)) Control Shift Enter

 

[canthony24]

That'll do it. There probably isn't a way around the CSE, right?