counting unique data based on date

[hiswillbedun]

I have attached a sheet that shows the serial number in column a. The date that parts were ordered is in column b. Then in the last column, it shows the age of our product when they needed to order parts. I don't care about counting the parts that they ordered. I just need to know how many times each serial number ordered parts on unique dates. I also want to know how old the product was each time that replacement parts had to be ordered. Please help.

Thanks in advance.

[Tony Valko]

To count the unique dates associated with a serial number...

L2 = some serial number

Then, this array formula**:

=SUM(IF(FREQUENCY(IF(A2:A7=L2,B2:B7),B2:B7),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Not sure I understand your other question.

[hiswillbedun]

My only issue is that I have over 2000 unique serial numbers. Is there a way to do this without specifying exact serial numbers as L2?

[Tony Valko]

You might be able to do it using a pivot table.

However, I never use pivot tables so I can't tell you how to set it up.

[hiswillbedun]

Ok. thanks. I figured out a way to do it with a manual process. Hopefully this is the only time that my company will ever need a report like this.

[Tony Valko]

Good deal. Thanks for the feedback!