Hi Ron,
Thanks for the help! That works great! Now one last question and I will
leave you alone. I have another column that I'd like to add to this formula.
In column C I have the product class. It can be either a 1, 2, or 3. What I
want to do is have the formula check this column for a 1 and only count the
invoices for only the rows that have a 1 in column C. It will count the
invoices by date but only count the invoices that have a 1 in column C. And
the same thing for the CM invoices also. How would I add this to the formula?

"Ron Coderre" wrote:

Try this:

Count of NON-"CM" Invoices
G2:
=SUMPRODUCT((LEFT($B$2:$B$20,2)<"CM")*($A$2:$A$20 =F2)/COUNTIF($B$2:$B$20,$B$2:$B$20&""))

Count of "CM" Invoices
H2:
=SUMPRODUCT((LEFT($B$2:$B$20,2)="CM")*($A$2:$A$20= F2)/COUNTIF($B$2:$B$20,$B$2:$B$20&""))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Secret Squirrel" wrote:

Hi Ron,

That seems to work fine, thank you. But now I have a follow up request.

In G2 I want to only count invoices that do not begin with "CM". And then I
want to add another formula in I2 that only counts invoices that begin with
"CM". This way I can separate invoices that are Credit Memos from regular
invoices. How would I re-write the code you sent and also create one to count
just CM's?

SS

"Ron Coderre" wrote:

Try something like this:

With
A1:A20 containing Dates (A1 holds the title)
B1:B20 containing Invoice Numbers (B1 holds the title)

Then
F1: Date
F2: 2/1/2007

G1: InvCount
G2:
=SUMPRODUCT((($B$2:$B$20<"")/COUNTIF($B$2:$B$20,$B$2:$B$20&""))*($A$2:$A$20=F2) )


Increment dates in Col_F
Copy G2 down as far as you need.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Secret Squirrel" wrote:

Sure...

Sheet1

Column A Column B Column C
(Inv Date) (Invoice #) (Line #)
2/1/2007 200112 1
2/1/2007 200112 2
2/1/2007 200113 1
2/2/2007 200115 1

On sheet 2 I want to count the number of invoices for each date above but
not count each line item as one invoice.

Sheet 2

Column A Column B
(Date) (Count)

2/1/2007 2
2/2/2007 1



"T. Valko" wrote:

Can you post a small sample of your data and the expected result?

Biff

"Secret Squirrel" wrote in
message ...
I have a spreadsheet that I track invoices by day. How do I count the
number
of invoices by each day of the month? Some of the invoices have multiple
line
items on them so I don't want to count those as individual invoices. I
only
want to count the invoices for a day and only count 1 of the multiple line
item invoices. This way it won't count each line item as an invoice for
that
day.

Column A has the invoice date and column B has the invoice number. I want
to
put this on a different worksheet and summarize it by day. I have a list
of
days on my summary worksheet in column A. So basically I'd want to match
the
date on my summary worksheet with the invoice date.

Any help would be greatly appreciated.

Thanks