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]

There are a number you can use. Some people use their own OneDrive accounts, etc, but there are other common free ones that people use, like DropBox.

Count PO's per Supplier.xlsx

Shared with Dropbox

www.dropbox.com

See if you can access the above.

 

[Joe4]

So, I downloaded your, clicked on cell F2 and hit the Enter key, and it updated the value correctly.
I then copied it down the whole page and it updated the rest, like this:

What happens if you go to cell F2, use the "F2" key to bring up the formula in editor mode, and hit Enter?

By the way, if you want the values in column E only listed once each, place this formula in cell E2:

=UNIQUE(A2:A13)

Then if you copy the formula in F2 down to cell F6, you should get this:

 

[Fluff]

Build version 2204 is quite old. If you type =choo into any cell do you get Choose as an option, or do you also get choosecols & chooserows?

 

[jlp]

When I went to cell F2 and pressed the F2 key I received the following:

 

[jlp]

Sorry - also when I copied the formula: =UNIQUE(A2:A13)
in E2 I received a SPILL error

 

[Joe4]

When I went to cell F2 and pressed the F2 key I received the following:

Yes, and now hit Enter to re-enter that formula. Does the value update then?

Sorry - also when I copied the formula: =UNIQUE(A2:A13)
in E2 I received a SPILL error

You need to delete everything below cell E2 in column E first, as it is a "Spill" formula that will automatically populate/fill the rest of the values.

I am guessing that your issue is what fluff is alluding to - you may be using an older version that does not have all of these functions yet.
It is odd, because when that happens, you would typically get a #NAME error instead of a value that is not correct.

 

[jlp]

Build version 2204 is quite old. If you type =choo into any cell do you get Choose as an option, or do you also get choosecols & chooserows?

When I type =choo I get Choose as an option

Sorry I didn't reply sooner - I didn't see your post

My apologies

 

[Fluff]

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)))

 

[jlp]

Yes, and now hit Enter to re-enter that formula. Does the value update then?


You need to delete everything below cell E2 in column E first, as it is a "Spill" formula that will automatically populate/fill the rest of the values.

I am guessing that your issue is what fluff is alluding to - you may be using an older version that does not have all of these functions yet.
It is odd, because when that happens, you would typically get a #NAME error instead of a value that is not correct.

The SPILL error went away when I removed the information under it and retyped the formula. When I hit <enter> I received Supplier A.
When I copied the formula down I received the following:

 

[Fluff]

Did you just hit Enter, or Ctrl Shift Enter?