Count PO's per Supplier

[jlp]

I know this has been asked a number of ways and I feel I have got myself so convoluted with so much information I cannot make heads or tails out of how I need to write this formula

I have a spreadsheet

Suppl Name	PO
Supplier A	41058
Supplier A	41099
Supplier A	41099
Supplier A	41166
Supplier A	41271
Supplier B	41059
Supplier B	41059
Supplier C	41052
Supplier D	41136
Supplier E	41157
Supplier E	41168
Supplier F	41139

I want the number of unique PO's per Supplier, such as:
Supplier A 4
Supplier B 1

For the life of me I cannot figure out how to do this.

Please help!

 

[MountainFog]

=LET(a,CHOOSECOLS(UNIQUE($A$2:$B$13),1),COUNTA(FILTER(a,a=$E2)))

Where E2 is the name of the supplier

 

[jlp]

=LET(a,CHOOSECOLS(UNIQUE($A$2:$B$13),1),COUNTA(FILTER(a,a=$E2)))

Where E2 is the name of the supplier

 

[jlp]

I appreciate the formula but I don't understand it

What is "LET" and "CHOOSECOLS"? Is E2 supposed to be the number of PO's?

Sorry for my confusion - as I said, "I've been looking at this all day!"

 

[MountainFog]

Let lets you assign a value to a variable that can be used later in the formula. In this case a =

CHOOSECOLS(UNIQUE($A$2:$B$13),1)

Unique takes the unique records of Suppl Name and PO, getting rid of duplicate records. Choose columns takes only the first column of that array.

You then filter it to get the list with only the name of the supplier. For example, E2 could equal Supplier A. CountA counts the number of values in that Array

 

[Joe4]

Note that you can find details on all the Excel functions, like LET and CHOOSECOLS simply by doing a Google Search on them.
Microsoft has documented all of the Excel functions, i.e.

LET function - Microsoft Support

support.microsoft.com

CHOOSECOLS function - Microsoft Support

support.microsoft.com

 

[jlp]

Let lets you assign a value to a variable that can be used later in the formula. In this case a =

CHOOSECOLS(UNIQUE($A$2:$B$13),1)

Unique takes the unique records of Suppl Name and PO, getting rid of duplicate records. Choose columns takes only the first column of that array.

You then filter it to get the list with only the name of the supplier. For example, E2 could equal Supplier A. CountA counts the number of values in that Array

,

 

[jlp]

When I copy that formula into my spreadsheet I receive "0" in Col E2 - shouldn't it return a value of 4, for 4 PO's made for Supplier A?

 

[Joe4]

MountainFog's solution seems to work for me on your data:

If you are getting zero, it probably means that you are referencing the wrong cell(s) in your formula, or your values in column A don't exactly match what is in cell E2.
They must match EXACTLY. Something as simple as an extra space will cause it not to match. I copied your value from cell A2 to cell E2 to make sure it matches exactly.

 

[jlp]

Ugh...I get what you're saying but now I'm getting a "1".

Is this an Array formula requiring Cntl+Alt+Enter?