Count Unique Values Occuring Per Period

[Doridriver]

Hi,

I've had a look for this but not quite found the solution that fits my problems

I've got a data set that looks like the following:

FY Period PO Number

11/12 3 48001490
11/12 3 48001495
11/12 4 48001494
11/12 4 48001494

I want to be able to count the number of times each period that a PO Number is used. I imagine this will take the form of a SUMIF formula where IF(FY=11/12, IF(Period=4 etc. etc.

Can anyone help?

Thanks

Peter

[arthurbr]

Hi and welcome to the board

A Pivot Table perhaps?

[Doridriver]

Hello and thanks for the swift response.

I'll have a look at that. Can I nest a FREQUENCY function in an IF formula?

[Doridriver]

Just tried the Pivot Table idea and I can extract the values I need but if I change the field settings to count it doesn't count unique values.

[NBVC]

Assuming your data is in A2:C5

And that you have a unique list of Period beginning in E2, then try in F2:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down

[Doridriver]

Thanks for the help. I get a bit lost in the FREQUENCY/MATCH/LEN functions so I've used a helper column which shows how many times each has been used and added a condition to my COUNTIF to only count the "1" values.

Many thanks all.

[lazyvba]

Hello,

I did write this code automating the count of unique items within pivot tables.
You could set up your pivot as desired and get the unique count updated.
Juste a paste to do in VBA.

http://lazyvba.blogspot.com/2010/11/...-to-count.html

Let me know if it's working

Lazyvba