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

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

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

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

Sir Ron,

its a nice formula,

when i change the SAMPLE date on A2 from 2/1/2007 into 1/2/2007
the count result is 1.5... for F2=2/1/2007i'm trying also how to fix it...
regards..
--
*****
birds of the same feather flock together..



"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

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

If one invoice number can have more that one date then that's correct....but,
the post didn't indicate that was the case.

***********
Regards,
Ron

XL2002, WinXP


"driller" wrote:

Sir Ron,

its a nice formula,

when i change the SAMPLE date on A2 from 2/1/2007 into 1/2/2007
the count result is 1.5... for F2=2/1/2007i'm trying also how to fix it...
regards..
--
*****
birds of the same feather flock together..



"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

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

That is correct. My invoice numbers do not have more than one date. There is
only one date for each invoice number. Thanks for the other responses. I'm
off to test things out. Thanks again Ron!

SS

"Ron Coderre" wrote:

If one invoice number can have more that one date then that's correct....but,
the post didn't indicate that was the case.

***********
Regards,
Ron

XL2002, WinXP


"driller" wrote:

Sir Ron,

its a nice formula,

when i change the SAMPLE date on A2 from 2/1/2007 into 1/2/2007
the count result is 1.5... for F2=2/1/2007i'm trying also how to fix it...
regards..
--
*****
birds of the same feather flock together..



"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

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

Actually I just noticed a minor flaw in the formula. It seems to be only
counting the invoices that do not have multiple line items. It's not even
counting 1 of the multiple line item invoices. It should count only one of
those multiple invoices.

"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

I don't know what to tell you.....the formulas work fine with my test data.
Maybe you should post your formulas for us to see.

Anyway.....Here's my latest test data and the 2 formulas you asked for:
Date Inv Type
1/1/2007 100 1
1/1/2007 200 2
1/1/2007 CM150 1
1/1/2007 CM250 2
1/1/2007 300 1
1/1/2007 300 1
1/1/2007 400 2
1/1/2007 CM500 1
1/1/2007 CM900 1

For 1/1/2007, there are 2 regular invoices coded 1 and 3 CM invoices coded 1

Here are the formulas:
Non-CM invoice count
G2:
=SUMPRODUCT(($C$2:$C$20=1)*(LEFT($B$2:$B$20,2)<"C M")*($A$2:$A$20=F2)/COUNTIF($B$2:$B$20,$B$2:$B$20&""))

CM invoice count
H2:
=SUMPRODUCT(($C$2:$C$20=1)*(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,
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

Hi Ron,

Here is the code I have:

=SUMPRODUCT((InvoicedSales!$W$4:$W$5000=1)*(LEFT(I nvoicedSales!$B$4:$B$5000,2)<"CM")*(InvoicedSales !$A$4:$A$5000=$A7)/COUNTIF(InvoicedSales!$B$4:$B$5000,InvoicedSales!$ B$4:$B$5000&""))

I'm using 2/1/2007 as my column A date.

Here's what I have in my columns:

Column A Column B Column W
2/1/2007 102014 1
2/1/2007 102015 1
2/1/2007 102015 1
2/1/2007 102011 1
2/1/2007 102012 1
2/1/2007 102013 1
2/1/2007 102014 1
2/1/2007 102009 2
2/1/2007 102010 2

On my summary sheet I have the following:

2/1/2007 with 1 in column w = 5
2/1/2007 with 2 in column w = 2

But there should be 6 with a 1 in column w not including the multiple
invoice twice.

Did I miss something in my formula? I checked it twice.

"Ron Coderre" wrote:

I don't know what to tell you.....the formulas work fine with my test data.
Maybe you should post your formulas for us to see.

Anyway.....Here's my latest test data and the 2 formulas you asked for:
Date Inv Type
1/1/2007 100 1
1/1/2007 200 2
1/1/2007 CM150 1
1/1/2007 CM250 2
1/1/2007 300 1
1/1/2007 300 1
1/1/2007 400 2
1/1/2007 CM500 1
1/1/2007 CM900 1

For 1/1/2007, there are 2 regular invoices coded 1 and 3 CM invoices coded 1

Here are the formulas:
Non-CM invoice count
G2:
=SUMPRODUCT(($C$2:$C$20=1)*(LEFT($B$2:$B$20,2)<"C M")*($A$2:$A$20=F2)/COUNTIF($B$2:$B$20,$B$2:$B$20&""))

CM invoice count
H2:
=SUMPRODUCT(($C$2:$C$20=1)*(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,
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

But there should be 6 with a 1 in column w not
including the multiple invoice twice.

There are 2 duplicates: 102014 and 102015.

There are 5 uniques that meet the criteria:

102014
102015
102011
102012
102013

Biff

"Secret Squirrel" wrote in
message ...
Hi Ron,

Here is the code I have:

=SUMPRODUCT((InvoicedSales!$W$4:$W$5000=1)*(LEFT(I nvoicedSales!$B$4:$B$5000,2)<"CM")*(InvoicedSales !$A$4:$A$5000=$A7)/COUNTIF(InvoicedSales!$B$4:$B$5000,InvoicedSales!$ B$4:$B$5000&""))

I'm using 2/1/2007 as my column A date.

Here's what I have in my columns:

Column A Column B Column W
2/1/2007 102014 1
2/1/2007 102015 1
2/1/2007 102015 1
2/1/2007 102011 1
2/1/2007 102012 1
2/1/2007 102013 1
2/1/2007 102014 1
2/1/2007 102009 2
2/1/2007 102010 2

On my summary sheet I have the following:

2/1/2007 with 1 in column w = 5
2/1/2007 with 2 in column w = 2

But there should be 6 with a 1 in column w not including the multiple
invoice twice.

Did I miss something in my formula? I checked it twice.

"Ron Coderre" wrote:

I don't know what to tell you.....the formulas work fine with my test
data.
Maybe you should post your formulas for us to see.

Anyway.....Here's my latest test data and the 2 formulas you asked for:
Date Inv Type
1/1/2007 100 1
1/1/2007 200 2
1/1/2007 CM150 1
1/1/2007 CM250 2
1/1/2007 300 1
1/1/2007 300 1
1/1/2007 400 2
1/1/2007 CM500 1
1/1/2007 CM900 1

For 1/1/2007, there are 2 regular invoices coded 1 and 3 CM invoices
coded 1

Here are the formulas:
Non-CM invoice count
G2:
=SUMPRODUCT(($C$2:$C$20=1)*(LEFT($B$2:$B$20,2)<"C M")*($A$2:$A$20=F2)/COUNTIF($B$2:$B$20,$B$2:$B$20&""))

CM invoice count
H2:
=SUMPRODUCT(($C$2:$C$20=1)*(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,
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

Duh! I must have had my head up my ass! Thanks for pointing that out!
Now I feel like a retard!

Sorry Ron, my bad....

"T. Valko" wrote:

But there should be 6 with a 1 in column w not
including the multiple invoice twice.

There are 2 duplicates: 102014 and 102015.

There are 5 uniques that meet the criteria:

102014
102015
102011
102012
102013

Biff

"Secret Squirrel" wrote in
message ...
Hi Ron,

Here is the code I have:

=SUMPRODUCT((InvoicedSales!$W$4:$W$5000=1)*(LEFT(I nvoicedSales!$B$4:$B$5000,2)<"CM")*(InvoicedSales !$A$4:$A$5000=$A7)/COUNTIF(InvoicedSales!$B$4:$B$5000,InvoicedSales!$ B$4:$B$5000&""))

I'm using 2/1/2007 as my column A date.

Here's what I have in my columns:

Column A Column B Column W
2/1/2007 102014 1
2/1/2007 102015 1
2/1/2007 102015 1
2/1/2007 102011 1
2/1/2007 102012 1
2/1/2007 102013 1
2/1/2007 102014 1
2/1/2007 102009 2
2/1/2007 102010 2

On my summary sheet I have the following:

2/1/2007 with 1 in column w = 5
2/1/2007 with 2 in column w = 2

But there should be 6 with a 1 in column w not including the multiple
invoice twice.

Did I miss something in my formula? I checked it twice.

"Ron Coderre" wrote:

I don't know what to tell you.....the formulas work fine with my test
data.
Maybe you should post your formulas for us to see.

Anyway.....Here's my latest test data and the 2 formulas you asked for:
Date Inv Type
1/1/2007 100 1
1/1/2007 200 2
1/1/2007 CM150 1
1/1/2007 CM250 2
1/1/2007 300 1
1/1/2007 300 1
1/1/2007 400 2
1/1/2007 CM500 1
1/1/2007 CM900 1

For 1/1/2007, there are 2 regular invoices coded 1 and 3 CM invoices
coded 1

Here are the formulas:
Non-CM invoice count
G2:
=SUMPRODUCT(($C$2:$C$20=1)*(LEFT($B$2:$B$20,2)<"C M")*($A$2:$A$20=F2)/COUNTIF($B$2:$B$20,$B$2:$B$20&""))

CM invoice count
H2:
=SUMPRODUCT(($C$2:$C$20=1)*(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,
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

We've all been there!

Biff

"Secret Squirrel" wrote in
message ...
Duh! I must have had my head up my ass! Thanks for pointing that out!
Now I feel like a retard!

Sorry Ron, my bad....

"T. Valko" wrote:

But there should be 6 with a 1 in column w not
including the multiple invoice twice.

There are 2 duplicates: 102014 and 102015.

There are 5 uniques that meet the criteria:

102014
102015
102011
102012
102013

Biff

"Secret Squirrel" wrote in
message ...
Hi Ron,

Here is the code I have:

=SUMPRODUCT((InvoicedSales!$W$4:$W$5000=1)*(LEFT(I nvoicedSales!$B$4:$B$5000,2)<"CM")*(InvoicedSales !$A$4:$A$5000=$A7)/COUNTIF(InvoicedSales!$B$4:$B$5000,InvoicedSales!$ B$4:$B$5000&""))

I'm using 2/1/2007 as my column A date.

Here's what I have in my columns:

Column A Column B Column W
2/1/2007 102014 1
2/1/2007 102015 1
2/1/2007 102015 1
2/1/2007 102011 1
2/1/2007 102012 1
2/1/2007 102013 1
2/1/2007 102014 1
2/1/2007 102009 2
2/1/2007 102010 2

On my summary sheet I have the following:

2/1/2007 with 1 in column w = 5
2/1/2007 with 2 in column w = 2

But there should be 6 with a 1 in column w not including the multiple
invoice twice.

Did I miss something in my formula? I checked it twice.

"Ron Coderre" wrote:

I don't know what to tell you.....the formulas work fine with my test
data.
Maybe you should post your formulas for us to see.

Anyway.....Here's my latest test data and the 2 formulas you asked
for:
Date Inv Type
1/1/2007 100 1
1/1/2007 200 2
1/1/2007 CM150 1
1/1/2007 CM250 2
1/1/2007 300 1
1/1/2007 300 1
1/1/2007 400 2
1/1/2007 CM500 1
1/1/2007 CM900 1

For 1/1/2007, there are 2 regular invoices coded 1 and 3 CM invoices
coded 1

Here are the formulas:
Non-CM invoice count
G2:
=SUMPRODUCT(($C$2:$C$20=1)*(LEFT($B$2:$B$20,2)<"C M")*($A$2:$A$20=F2)/COUNTIF($B$2:$B$20,$B$2:$B$20&""))

CM invoice count
H2:
=SUMPRODUCT(($C$2:$C$20=1)*(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,
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

Apologize? For misinterpreting the data?....Not a problem. It happens to all
of us.

Now...about incrementally adding more criteria each time I thought I had the
correct formula figured out....<vbg

Seriously, though....I'm glad we worked out something you could use.
And thanks for the feedback....very much appreciated.

***********
Regards,
Ron

XL2002, WinXP


"Secret Squirrel" wrote:

Duh! I must have had my head up my ass! Thanks for pointing that out!
Now I feel like a retard!

Sorry Ron, my bad....

"T. Valko" wrote:

But there should be 6 with a 1 in column w not
including the multiple invoice twice.

There are 2 duplicates: 102014 and 102015.

There are 5 uniques that meet the criteria:

102014
102015
102011
102012
102013

Biff

"Secret Squirrel" wrote in
message ...
Hi Ron,

Here is the code I have:

=SUMPRODUCT((InvoicedSales!$W$4:$W$5000=1)*(LEFT(I nvoicedSales!$B$4:$B$5000,2)<"CM")*(InvoicedSales !$A$4:$A$5000=$A7)/COUNTIF(InvoicedSales!$B$4:$B$5000,InvoicedSales!$ B$4:$B$5000&""))

I'm using 2/1/2007 as my column A date.

Here's what I have in my columns:

Column A Column B Column W
2/1/2007 102014 1
2/1/2007 102015 1
2/1/2007 102015 1
2/1/2007 102011 1
2/1/2007 102012 1
2/1/2007 102013 1
2/1/2007 102014 1
2/1/2007 102009 2
2/1/2007 102010 2

On my summary sheet I have the following:

2/1/2007 with 1 in column w = 5
2/1/2007 with 2 in column w = 2

But there should be 6 with a 1 in column w not including the multiple
invoice twice.

Did I miss something in my formula? I checked it twice.

"Ron Coderre" wrote:

I don't know what to tell you.....the formulas work fine with my test
data.
Maybe you should post your formulas for us to see.

Anyway.....Here's my latest test data and the 2 formulas you asked for:
Date Inv Type
1/1/2007 100 1
1/1/2007 200 2
1/1/2007 CM150 1
1/1/2007 CM250 2
1/1/2007 300 1
1/1/2007 300 1
1/1/2007 400 2
1/1/2007 CM500 1
1/1/2007 CM900 1

For 1/1/2007, there are 2 regular invoices coded 1 and 3 CM invoices
coded 1

Here are the formulas:
Non-CM invoice count
G2:
=SUMPRODUCT(($C$2:$C$20=1)*(LEFT($B$2:$B$20,2)<"C M")*($A$2:$A$20=F2)/COUNTIF($B$2:$B$20,$B$2:$B$20&""))

CM invoice count
H2:
=SUMPRODUCT(($C$2:$C$20=1)*(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,
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

Sorry about all the additions to the formula. Every time I sent it off to my
boss he would add another request. Bosses can be very demanding sometimes!
This post is now complete. Thank you again!

SS

"Ron Coderre" wrote:

Apologize? For misinterpreting the data?....Not a problem. It happens to all
of us.

Now...about incrementally adding more criteria each time I thought I had the
correct formula figured out....<vbg

Seriously, though....I'm glad we worked out something you could use.
And thanks for the feedback....very much appreciated.

***********
Regards,
Ron

XL2002, WinXP


"Secret Squirrel" wrote:

Duh! I must have had my head up my ass! Thanks for pointing that out!
Now I feel like a retard!

Sorry Ron, my bad....

"T. Valko" wrote:

But there should be 6 with a 1 in column w not
including the multiple invoice twice.

There are 2 duplicates: 102014 and 102015.

There are 5 uniques that meet the criteria:

102014
102015
102011
102012
102013

Biff

"Secret Squirrel" wrote in
message ...
Hi Ron,

Here is the code I have:

=SUMPRODUCT((InvoicedSales!$W$4:$W$5000=1)*(LEFT(I nvoicedSales!$B$4:$B$5000,2)<"CM")*(InvoicedSales !$A$4:$A$5000=$A7)/COUNTIF(InvoicedSales!$B$4:$B$5000,InvoicedSales!$ B$4:$B$5000&""))

I'm using 2/1/2007 as my column A date.

Here's what I have in my columns:

Column A Column B Column W
2/1/2007 102014 1
2/1/2007 102015 1
2/1/2007 102015 1
2/1/2007 102011 1
2/1/2007 102012 1
2/1/2007 102013 1
2/1/2007 102014 1
2/1/2007 102009 2
2/1/2007 102010 2

On my summary sheet I have the following:

2/1/2007 with 1 in column w = 5
2/1/2007 with 2 in column w = 2

But there should be 6 with a 1 in column w not including the multiple
invoice twice.

Did I miss something in my formula? I checked it twice.

"Ron Coderre" wrote:

I don't know what to tell you.....the formulas work fine with my test
data.
Maybe you should post your formulas for us to see.

Anyway.....Here's my latest test data and the 2 formulas you asked for:
Date Inv Type
1/1/2007 100 1
1/1/2007 200 2
1/1/2007 CM150 1
1/1/2007 CM250 2
1/1/2007 300 1
1/1/2007 300 1
1/1/2007 400 2
1/1/2007 CM500 1
1/1/2007 CM900 1

For 1/1/2007, there are 2 regular invoices coded 1 and 3 CM invoices
coded 1

Here are the formulas:
Non-CM invoice count
G2:
=SUMPRODUCT(($C$2:$C$20=1)*(LEFT($B$2:$B$20,2)<"C M")*($A$2:$A$20=F2)/COUNTIF($B$2:$B$20,$B$2:$B$20&""))

CM invoice count
H2:
=SUMPRODUCT(($C$2:$C$20=1)*(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,
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