Calculate number Invoices

[Graeme155]

I have a spreadsheet that lists Invoices showing Sales and COGS.

This is in a pivot tables that filters the list by month and then Salesperson - then shows each Invoice Number.

i.e

Month
Salesperson
1253
1265
2222 etc

and I want a formula to count the number of Sales for each Salesperson by month.

Any help appreciated.

 

[CWatts]

If you take either the invoice number or the sales person name, and move it into your values section of your pivot and have it set as "count of" that will show you the number of invoices that the sales person created.

This will work unless you want to see total units in which you'd have to have a quantify column in your source data and set your pivot to sum of Quantity.

 

[Graeme155]

Thanks C

That's brilliant.

Graeme

 

[Graeme155]

One thing I didn't mention was that each line of the Invoice appears in the table a number of times. Even though I only show the single Invoice Line in this Pivot Table it is counting the Invoice Number each time it appears.

What I am after now is to count unique Invoice Numbers.

Any ideas appreciated.

Graeme

 

[CWatts]

Unfortunately not specifically in a pivot table. Without an example or template of how your data is structured, I'm kind of shooting blind but maybe this will help.

The only real thing you could do is set up some sort of helper column in your source data to count unique numbers and use that in your pivot. If you do go this route, if you insert this in the middle of your pivot range, then you wont have to modify it, but if you add it to a column outside of your pivot range, you'll have to make sure you adjust your pivot source. (And don't forget to refresh.) For example, if column A is your invoice numbers, you can check if they're unique with the formulas in column B.

Sheet1

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="text-align:center; ">Invoice</td><td style="text-align:center; ">Unique?</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:center; ">9944</td><td style="text-align:center; ">1</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:center; ">9709</td><td style="text-align:center; ">1</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:center; ">9726</td><td style="text-align:center; ">1</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:center; ">9839</td><td style="text-align:center; ">1</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="text-align:center; ">9726</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style="text-align:center; ">9755</td><td style="text-align:center; ">1</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td style="text-align:center; ">9623</td><td style="text-align:center; ">1</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td style="text-align:center; ">9496</td><td style="text-align:center; ">1</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td style="text-align:center; ">9709</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td style="text-align:center; ">9089</td><td style="text-align:center; ">1</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">12</td><td style="text-align:center; ">9393</td><td style="text-align:center; ">1</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">13</td><td style="text-align:center; ">9130</td><td style="text-align:center; ">1</td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>B2</td><td>=IF(COUNTIF($A$1:A2,A2)<>1,0,1)</td></tr><tr><td>B3</td><td>=IF(COUNTIF($A$1:A3,A3)<>1,0,1)</td></tr><tr><td>B4</td><td>=IF(COUNTIF($A$1:A4,A4)<>1,0,1)</td></tr><tr><td>B5</td><td>=IF(COUNTIF($A$1:A5,A5)<>1,0,1)</td></tr><tr><td>B6</td><td>=IF(COUNTIF($A$1:A6,A6)<>1,0,1)</td></tr><tr><td>B7</td><td>=IF(COUNTIF($A$1:A7,A7)<>1,0,1)</td></tr><tr><td>B8</td><td>=IF(COUNTIF($A$1:A8,A8)<>1,0,1)</td></tr><tr><td>B9</td><td>=IF(COUNTIF($A$1:A9,A9)<>1,0,1)</td></tr><tr><td>B10</td><td>=IF(COUNTIF($A$1:A10,A10)<>1,0,1)</td></tr><tr><td>B11</td><td>=IF(COUNTIF($A$1:A11,A11)<>1,0,1)</td></tr><tr><td>B12</td><td>=IF(COUNTIF($A$1:A12,A12)<>1,0,1)</td></tr><tr><td>B13</td><td>=IF(COUNTIF($A$1:A13,A13)<>1,0,1)</td></tr></tbody></table></td></tr></tbody></table>

The formula checks to see if the invoice number is anywhere above it, if it is unique, it gives it a 1 value. If it's a duplicate, then it gives it a 0 value. When we then use the sum option in the pivot field, that will give us a count of unique numbers which would look like this:

Sheet4

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:91px;"><col style="width:109px;"><col style="width:107px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>
</td><td>Values</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>Row Labels</td><td>Count of Invoice</td><td>Sum of Unique?</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:left; ">9089</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="text-align:left; ">9130</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style="text-align:left; ">9393</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td style="text-align:left; ">9496</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td style="text-align:left; ">9623</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td style="text-align:left; ">9709</td><td style="text-align:right; ">2</td><td style="text-align:right; ">1</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td style="text-align:left; ">9726</td><td style="text-align:right; ">2</td><td style="text-align:right; ">1</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">12</td><td style="text-align:left; ">9755</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">13</td><td style="text-align:left; ">9839</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">14</td><td style="text-align:left; ">9920</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">15</td><td style="text-align:left; ">Grand Total</td><td style="text-align:right; ">12</td><td style="text-align:right; ">10</td></tr></tbody></table>

 

[Aladin Akyurek]

One thing I didn't mention was that each line of the Invoice appears in the table a number of times. Even though I only show the single Invoice Line in this Pivot Table it is counting the Invoice Number each time it appears.

What I am after now is to count unique Invoice Numbers.

Any ideas appreciated.

Graeme

See whether the following by Mark W. helps...

http://www.mrexcel.com/forum/showthread.php?t=43396 (post #3)