| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for certain months and summing the totals
Hi
I need a formula to first look for all of january date, febuary dates, etc. and total them under check amount column. Hope this is clear.thank you Amount Date Check Amount Month $320 1/29/2008 $1,886.24 Jan $40 1/29/2008 Feb $140 1/29/2008 Mar $80 1/29/2008 Apr $80 1/29/2008 May $1,200 1/29/2008 Jun $280 2/30/2008 Jul $1,200 2/31/2008 Aug $80 3/1/2008 Sep |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Looking for certain months and summing the totals
=sumproduct((month(b2:b22)=1)*c2:c22)
to add the year =sumproduct((year(b2:b22)=2008)*(month(b2:b22)=1)* c2:c22) -- Don Guillett Microsoft MVP Excel SalesAid Software "sherobot" wrote in message ... Hi I need a formula to first look for all of january date, febuary dates, etc. and total them under check amount column. Hope this is clear.thank you Amount Date Check Amount Month $320 1/29/2008 $1,886.24 Jan $40 1/29/2008 Feb $140 1/29/2008 Mar $80 1/29/2008 Apr $80 1/29/2008 May $1,200 1/29/2008 Jun $280 2/30/2008 Jul $1,200 2/31/2008 Aug $80 3/1/2008 Sep |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Looking for certain months and summing the totals
Ok Thank you. I think I understand the logic, but keep getting error #Value!
"Don Guillett" wrote: =sumproduct((month(b2:b22)=1)*c2:c22) to add the year =sumproduct((year(b2:b22)=2008)*(month(b2:b22)=1)* c2:c22) -- Don Guillett Microsoft MVP Excel SalesAid Software "sherobot" wrote in message ... Hi I need a formula to first look for all of january date, febuary dates, etc. and total them under check amount column. Hope this is clear.thank you Amount Date Check Amount Month $320 1/29/2008 $1,886.24 Jan $40 1/29/2008 Feb $140 1/29/2008 Mar $80 1/29/2008 Apr $80 1/29/2008 May $1,200 1/29/2008 Jun $280 2/30/2008 Jul $1,200 2/31/2008 Aug $80 3/1/2008 Sep |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Looking for certain months and summing the totals
hmmm still getting the value error. not sure what I'm doing wrong.
so you are taking the =sumproduct((month(b2:b9)=1)*A2:A9) where column b has the date and column a has the amount right? Amount Date $320 1/29/2008 $40 1/29/2008 $140 1/29/2008 $80 1/29/2008 $80 1/29/2008 $1,200 1/29/2008 $280 2/30/2008 $1,200 2/31/2008 $80 3/1/2008 "Don Guillett" wrote: I used c as your values column when it should have been col A =sumproduct((month(b2:b22)=1)*c2:c22) =sumproduct((month(b2:b22)=1)*A2:A22) -- Don Guillett Microsoft MVP Excel SalesAid Software "sherobot" wrote in message ... Ok Thank you. I think I understand the logic, but keep getting error #Value! "Don Guillett" wrote: =sumproduct((month(b2:b22)=1)*c2:c22) to add the year =sumproduct((year(b2:b22)=2008)*(month(b2:b22)=1)* c2:c22) -- Don Guillett Microsoft MVP Excel SalesAid Software "sherobot" wrote in message ... Hi I need a formula to first look for all of january date, febuary dates, etc. and total them under check amount column. Hope this is clear.thank you Amount Date Check Amount Month $320 1/29/2008 $1,886.24 Jan $40 1/29/2008 Feb $140 1/29/2008 Mar $80 1/29/2008 Apr $80 1/29/2008 May $1,200 1/29/2008 Jun $280 2/30/2008 Jul $1,200 2/31/2008 Aug $80 3/1/2008 Sep |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Looking for certain months and summing the totals
Maybe your dollar amounts are text values and not proper numbers.
Pete On Jan 30, 4:22*pm, sherobot wrote: hmmm still getting the value error. not sure what I'm doing wrong. so you are taking the =sumproduct((month(b2:b9)=1)*A2:A9) where column b has the date and column a has the amount right? Amount *Date *$320 * 1/29/2008 *$40 * *1/29/2008 *$140 * 1/29/2008 *$80 * *1/29/2008 *$80 * *1/29/2008 *$1,200 * * * * 1/29/2008 *$280 * 2/30/2008 *$1,200 * * * * 2/31/2008 *$80 * *3/1/2008 "Don Guillett" wrote: I used c as your values column when it should have been col A =sumproduct((month(b2:b22)=1)*c2:c22) =sumproduct((month(b2:b22)=1)*A2:A22) -- Don Guillett Microsoft MVP Excel SalesAid Software "sherobot" wrote in message ... Ok Thank you. I think I understand the logic, but keep getting error #Value! "Don Guillett" wrote: =sumproduct((month(b2:b22)=1)*c2:c22) to add the year =sumproduct((year(b2:b22)=2008)*(month(b2:b22)=1)* c2:c22) -- Don Guillett Microsoft MVP Excel SalesAid Software "sherobot" wrote in message ... Hi I need a formula to first look for all of january date, febuary dates, etc. and total them under check amount column. Hope this is clear.thank you Amount Date * Check Amount Month $320 1/29/2008 * *$1,886.24 Jan $40 1/29/2008 Feb $140 1/29/2008 Mar $80 1/29/2008 Apr $80 1/29/2008 May $1,200 1/29/2008 Jun $280 2/30/2008 Jul $1,200 2/31/2008 Aug $80 3/1/2008 Sep- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Looking for certain months and summing the totals
|
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Looking for certain months and summing the totals
Double checked and they are accounting. Changed it to currency and still have
the error. I also checked the date column. It's listed as date. Still getting the value error "Pete_UK" wrote: Maybe your dollar amounts are text values and not proper numbers. Pete On Jan 30, 4:22 pm, sherobot wrote: hmmm still getting the value error. not sure what I'm doing wrong. so you are taking the =sumproduct((month(b2:b9)=1)*A2:A9) where column b has the date and column a has the amount right? Amount Date $320 1/29/2008 $40 1/29/2008 $140 1/29/2008 $80 1/29/2008 $80 1/29/2008 $1,200 1/29/2008 $280 2/30/2008 $1,200 2/31/2008 $80 3/1/2008 "Don Guillett" wrote: I used c as your values column when it should have been col A =sumproduct((month(b2:b22)=1)*c2:c22) =sumproduct((month(b2:b22)=1)*A2:A22) -- Don Guillett Microsoft MVP Excel SalesAid Software "sherobot" wrote in message ... Ok Thank you. I think I understand the logic, but keep getting error #Value! "Don Guillett" wrote: =sumproduct((month(b2:b22)=1)*c2:c22) to add the year =sumproduct((year(b2:b22)=2008)*(month(b2:b22)=1)* c2:c22) -- Don Guillett Microsoft MVP Excel SalesAid Software "sherobot" wrote in message ... Hi I need a formula to first look for all of january date, febuary dates, etc. and total them under check amount column. Hope this is clear.thank you Amount Date Check Amount Month $320 1/29/2008 $1,886.24 Jan $40 1/29/2008 Feb $140 1/29/2008 Mar $80 1/29/2008 Apr $80 1/29/2008 May $1,200 1/29/2008 Jun $280 2/30/2008 Jul $1,200 2/31/2008 Aug $80 3/1/2008 Sep- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Looking for certain months and summing the totals
ok I just did it on a new worksheet and threw in random numbers and dates and
it works perfectly! So it must be something wrong with the data on my sheet. But now that I know it works, I'll start with a clean worksheet. Hopefully copy and paste it but if not I can type it in (it's not a lot) Anyway THANK gUYS!!!!!! it helped a bunch!!! "sherobot" wrote: Double checked and they are accounting. Changed it to currency and still have the error. I also checked the date column. It's listed as date. Still getting the value error "Pete_UK" wrote: Maybe your dollar amounts are text values and not proper numbers. Pete On Jan 30, 4:22 pm, sherobot wrote: hmmm still getting the value error. not sure what I'm doing wrong. so you are taking the =sumproduct((month(b2:b9)=1)*A2:A9) where column b has the date and column a has the amount right? Amount Date $320 1/29/2008 $40 1/29/2008 $140 1/29/2008 $80 1/29/2008 $80 1/29/2008 $1,200 1/29/2008 $280 2/30/2008 $1,200 2/31/2008 $80 3/1/2008 "Don Guillett" wrote: I used c as your values column when it should have been col A =sumproduct((month(b2:b22)=1)*c2:c22) =sumproduct((month(b2:b22)=1)*A2:A22) -- Don Guillett Microsoft MVP Excel SalesAid Software "sherobot" wrote in message ... Ok Thank you. I think I understand the logic, but keep getting error #Value! "Don Guillett" wrote: =sumproduct((month(b2:b22)=1)*c2:c22) to add the year =sumproduct((year(b2:b22)=2008)*(month(b2:b22)=1)* c2:c22) -- Don Guillett Microsoft MVP Excel SalesAid Software "sherobot" wrote in message ... Hi I need a formula to first look for all of january date, febuary dates, etc. and total them under check amount column. Hope this is clear.thank you Amount Date Check Amount Month $320 1/29/2008 $1,886.24 Jan $40 1/29/2008 Feb $140 1/29/2008 Mar $80 1/29/2008 Apr $80 1/29/2008 May $1,200 1/29/2008 Jun $280 2/30/2008 Jul $1,200 2/31/2008 Aug $80 3/1/2008 Sep- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Looking for certain months and summing the totals
OK you know what it was...wow this is embarassing.
I had 2/30/08 or 2/31/08. I put in actual dates and it's fine. btw 29 days in feb this year because it's a leap year. wow sorry to have wasted so much of your time. :( "sherobot" wrote: ok I just did it on a new worksheet and threw in random numbers and dates and it works perfectly! So it must be something wrong with the data on my sheet. But now that I know it works, I'll start with a clean worksheet. Hopefully copy and paste it but if not I can type it in (it's not a lot) Anyway THANK gUYS!!!!!! it helped a bunch!!! "sherobot" wrote: Double checked and they are accounting. Changed it to currency and still have the error. I also checked the date column. It's listed as date. Still getting the value error "Pete_UK" wrote: Maybe your dollar amounts are text values and not proper numbers. Pete On Jan 30, 4:22 pm, sherobot wrote: hmmm still getting the value error. not sure what I'm doing wrong. so you are taking the =sumproduct((month(b2:b9)=1)*A2:A9) where column b has the date and column a has the amount right? Amount Date $320 1/29/2008 $40 1/29/2008 $140 1/29/2008 $80 1/29/2008 $80 1/29/2008 $1,200 1/29/2008 $280 2/30/2008 $1,200 2/31/2008 $80 3/1/2008 "Don Guillett" wrote: I used c as your values column when it should have been col A =sumproduct((month(b2:b22)=1)*c2:c22) =sumproduct((month(b2:b22)=1)*A2:A22) -- Don Guillett Microsoft MVP Excel SalesAid Software "sherobot" wrote in message ... Ok Thank you. I think I understand the logic, but keep getting error #Value! "Don Guillett" wrote: =sumproduct((month(b2:b22)=1)*c2:c22) to add the year =sumproduct((year(b2:b22)=2008)*(month(b2:b22)=1)* c2:c22) -- Don Guillett Microsoft MVP Excel SalesAid Software "sherobot" wrote in message ... Hi I need a formula to first look for all of january date, febuary dates, etc. and total them under check amount column. Hope this is clear.thank you Amount Date Check Amount Month $320 1/29/2008 $1,886.24 Jan $40 1/29/2008 Feb $140 1/29/2008 Mar $80 1/29/2008 Apr $80 1/29/2008 May $1,200 1/29/2008 Jun $280 2/30/2008 Jul $1,200 2/31/2008 Aug $80 3/1/2008 Sep- Hide quoted text - - Show quoted text - |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Looking for certain months and summing the totals
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Large Time totals not summing. | Excel Worksheet Functions | |||
| Add Totals for Last 12 Months | Excel Discussion (Misc queries) | |||
| Summing Weekly Totals into Monthly Totals | Excel Worksheet Functions | |||
| How can I subtotal my weekly totals by months? | Excel Worksheet Functions | |||
| Help with Summing Up Totals | Excel Discussion (Misc queries) | |||
Linear Mode
