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


[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Vendor Name[/TD]
[TD]Minority Supplier[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Goya[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Goya[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Monaco[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Shasta[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Nogales[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

3​

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

 

[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?