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!

 

[jlp]

That means you don't have choosecols, although as Joe said, I'm surprised you don't get a #name error.
Try

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

THAT WORKS!!!!

Did you just hit Enter, or Ctrl Shift Enter?

Enter

 

[Joe4]

That means you don't have choosecols, although as Joe said, I'm surprised you don't get a #name error.

Yeah, that is what was throwing me off. Had we seen that error (like we typically do), I would have expected that they did not have all the functions used in that formula.
But since they have 365 listed as their Office Version, and were not getting any errors, I discounted that as a possibility.
Now I know better!

Anyway, glad we were able to get to the bottom of it and you have a working solution now (thanks for the assist, Fluff!).

 

[jlp]

EVERYTHING IS WORKING NOW!!!

Thank you, thank you, thank you!

This Formula worked:
=LET(a,INDEX(UNIQUE($A$2:$B$13),,1),COUNTA(FILTER(a,a=$E2)))

And this one worked when I did Cntl+Shift+Enter

 

[Fluff]

Do not use Ctrl shift enter, it is not needed in 365.

 

[Joe4]

jlp,
When marking a post as the solution, we want to mark the post that actually contains the formula that worked for you.
I re-marked it to Fluff's formula, as that is the one that worked for you.

 

[jlp]

jlp,
When marking a post as the solution, we want to mark the post that actually contains the formula that worked for you.
I re-marked it to Fluff's formula, as that is the one that worked for you.

Thank you...I was trying to mark the correct one I guess I missed the "marked"

 

[jlp]

One more question (I hope).

I now want to do the same thing, but count a different column looking for the word "TRUE".
Basically this:
For Supplier A how many times does the word "TRUE" appear in Col XX

 

[MountainFog]

One more question (I hope).

I now want to do the same thing, but count a different column looking for the word "TRUE".
Basically this:
For Supplier A how many times does the word "TRUE" appear in Col XX

Is it possible for this situation to occur? This will assist in creating a solution.

A​	B​	XX​
Supplier A	41099	TRUE
Supplier A	41099	FALSE

 

[jlp]

Sorry...you have me thinking...one PO can have several receipts against it. Of those receipts some could have been "On time (false)" and some could have been "Late (True)".

So...I have the number of PO's per Supplier but the number of receipts are NOT a 1:1 comparison. So out of the number of receipts per supplier I would want to know how many receipts were late and how many were on time.

See example here:
OOP's a little too much info in that last spreadsheet - here is a simplified version

Count Lates per Supplier 2.xlsx

Shared with Dropbox

www.dropbox.com

 

[MountainFog]

So, you are no longer looking for unique PO #, then?

If so:
* To get the number of Receipts per Supplier:

=COUNTIF($A$2:$A$16,"Supplier A")

* To get the number of Receipts per Supplier that were on time:

=SUM(FILTER(IF($E$2:$E$16<=$D$2:$D$16,1,0),$A$2:$A$16="Supplier A"))

* To get the number of Receipts per Supplier that were late:

=SUM(FILTER(IF($E$2:$E$16<=$D$2:$D$16,0,1),$A$2:$A$16="Supplier A"))