Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count the numbers in a report
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count the numbers in a report
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "vishy" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count the numbers in a report
Debra Dalgleish shows one way:
http://contextures.com/xlPivot07.html#Unique vishy wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create a cycle count report | Excel Worksheet Functions | |||
Report Numbers only | Excel Discussion (Misc queries) | |||
Need to report a count that meets multiple criteria. | Excel Worksheet Functions | |||
Find and report matching numbers in two worksheets | Excel Discussion (Misc queries) | |||
How do I insert page numbers on an Excel report. | Excel Discussion (Misc queries) |