Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count invoices by day
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count invoices by day
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count invoices by day
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count invoices by day
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count invoices by day
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count invoices by day
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count invoices by day
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count invoices by day
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need formula to count spinoffs | Excel Worksheet Functions | |||
Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Count Intervals of 1 Numeric value in a Row and Return Count down Column | Excel Worksheet Functions | |||
Pivot Tables - "simple" question | Excel Discussion (Misc queries) | |||
Conversion | Excel Worksheet Functions |