Counting Numbers

[SDW]

Good morning
One of the task I have at the end of the month is to determine how many
invoices I have raised for a particular area
To do this the sales are exported into Excel
Where the problem lies is if I sell 5 things to a clients it lists the
invoice number 5 times and the item price separately
What I would like to do is count the number of invoices once per invoice
number
Therefore:
10322
10322
10333
10395
10395

Would equal 3 sales - any suggestions?

The invoice number are not always sequential, however in the exported format
are all in col. A

Many thanks

Stephen West
Gold Coast, Australia

 

[Aladin Akyurek]

=SUMPRODUCT((InvoiceRange<>"")/COUNTIF(InvoiceRange,InvoiceRange&""))

 

[Bob Phillips]

Stephen,

Assuming it is all numeric, then try this

=SUM(IF(FREQUENCY(A:A,A:A)>0,1,0))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[A.W.J. Ales]

Aladin,

Just for my curiosity : Is there any reason for the : &"" in the COUNTIF
part.
I can't reason why and copying the formula without it, thus :
=SUMPRODUCT((InvoiceRange<>"")/COUNTIF(InvoiceRange,InvoiceRange)) works
well.
(This applies for both numbers as for texts in the InvoiceRange)

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[A.W.J. Ales]

No need to anwer Aladdin.
I've already sorted it out.
Didn't think of the possibility of blanks cells in the InvoiceRange at
first.

Clever formula by the way !!!

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Aladin Akyurek]

Clever formula by the way !!!

http://www.mrexcel.com/board2/viewtopic.php?t=73502&highlight=grove

includes a bit about its "history" and attempts to explain how it works.

 

[SDW]

Bob
I assume I will be replacing the (A:A, A:A) with the cell parameters
If it is from (say) A13 to A299 can you advise syntax
Many thanks
Stephen

 

[Bob Phillips]

Stephen,

If there is nothing else in column A, the formula is okay as it stands. If
there is, then use

=SUM(IF(FREQUENCY(A13:A299,A13:A299)>0,1,0))

as I think you suspected.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)