How to count the numbers in a report

[vishy]

Hi,

from the following type of report is downloaded from SAP
Quotation No. Item No. Item Value
402000015 100 240,000.00
402000032 100 220,000.00
402000037 100 45,000.00
402000038 100 70,000.00
402000039 100 250,000.00
402000039 200 75,000.00
402000039 300 100,000.00
402000039 400 250,000.00

I would like to make a pivot table from the above report. In the pivot table
i wanted to have the count of quotation numbers. But the problem is based on
the item numbers the quotation numbers is repeated, hence the normal count
function will consider the repeated numbers also .

Is there any way out to correctly count the number of quotations

Pl let me know if you have the answer.

Thanks & regards

Vish

 

[Bernard Liengme]

Assuming that the column A holds the Quotation numbers and that A1 is a
label
Use =SUMPRODUCT((A2:A99<>"")/COUNTIF(A2:A99,A2:A99&""))
to count the number of unique entries
best wishes

 

[Dave Peterson]

Debra Dalgleish shows one way:
http://contextures.com/xlPivot07.html#Unique