|
Using the Month function to sum another column
I have a column with dates and another with totals. I want to for every month
(01 - 12) to search all the rows in columnA and sum the totals only relevent to the month from columnB. E.g for OCtober the total is 1500 ColumnA columnB 10/10/04 1000 10/10/04 500 10/09/04 250 |
Hi
try =SUMPRODUCT(--(MONTH(A1:A100=10),--(YEAR(A1:A100=2004),B1:B100) -- Regards Frank Kabel Frankfurt, Germany "Chris" schrieb im Newsbeitrag ... I have a column with dates and another with totals. I want to for every month (01 - 12) to search all the rows in columnA and sum the totals only relevent to the month from columnB. E.g for OCtober the total is 1500 ColumnA columnB 10/10/04 1000 10/10/04 500 10/09/04 250 |
Hi Frank
this gives me an error. I've changed the A1:100 ranges to my one. Any ideas. Using 2002 version. Also what do the -- mean Cherers Chris "Frank Kabel" wrote: Hi try =SUMPRODUCT(--(MONTH(A1:A100=10),--(YEAR(A1:A100=2004),B1:B100) -- Regards Frank Kabel Frankfurt, Germany "Chris" schrieb im Newsbeitrag ... I have a column with dates and another with totals. I want to for every month (01 - 12) to search all the rows in columnA and sum the totals only relevent to the month from columnB. E.g for OCtober the total is 1500 ColumnA columnB 10/10/04 1000 10/10/04 500 10/09/04 250 |
Hi
what is the formula you have used and what exact error do you get. Note: you can't use a range A:A together with SUMPRODUCT. For an explanation see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany Chris wrote: Hi Frank this gives me an error. I've changed the A1:100 ranges to my one. Any ideas. Using 2002 version. Also what do the -- mean Cherers Chris "Frank Kabel" wrote: Hi try =SUMPRODUCT(--(MONTH(A1:A100=10),--(YEAR(A1:A100=2004),B1:B100) -- Regards Frank Kabel Frankfurt, Germany "Chris" schrieb im Newsbeitrag ... I have a column with dates and another with totals. I want to for every month (01 - 12) to search all the rows in columnA and sum the totals only relevent to the month from columnB. E.g for OCtober the total is 1500 ColumnA columnB 10/10/04 1000 10/10/04 500 10/09/04 250 |
For my sins I used what you said below (I am a novice at this) I used a
range. I need to total all the amounts that have a month of say 04 or 10 associated. The error I get is the standard formula error. If that makes sense? "Frank Kabel" wrote: Hi what is the formula you have used and what exact error do you get. Note: you can't use a range A:A together with SUMPRODUCT. For an explanation see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany Chris wrote: Hi Frank this gives me an error. I've changed the A1:100 ranges to my one. Any ideas. Using 2002 version. Also what do the -- mean Cherers Chris "Frank Kabel" wrote: Hi try =SUMPRODUCT(--(MONTH(A1:A100=10),--(YEAR(A1:A100=2004),B1:B100) -- Regards Frank Kabel Frankfurt, Germany "Chris" schrieb im Newsbeitrag ... I have a column with dates and another with totals. I want to for every month (01 - 12) to search all the rows in columnA and sum the totals only relevent to the month from columnB. E.g for OCtober the total is 1500 ColumnA columnB 10/10/04 1000 10/10/04 500 10/09/04 250 |
Hi
just post the formula you have tried -- Regards Frank Kabel Frankfurt, Germany Chris wrote: For my sins I used what you said below (I am a novice at this) I used a range. I need to total all the amounts that have a month of say 04 or 10 associated. The error I get is the standard formula error. If that makes sense? "Frank Kabel" wrote: Hi what is the formula you have used and what exact error do you get. Note: you can't use a range A:A together with SUMPRODUCT. For an explanation see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany Chris wrote: Hi Frank this gives me an error. I've changed the A1:100 ranges to my one. Any ideas. Using 2002 version. Also what do the -- mean Cherers Chris "Frank Kabel" wrote: Hi try =SUMPRODUCT(--(MONTH(A1:A100=10),--(YEAR(A1:A100=2004),B1:B100) -- Regards Frank Kabel Frankfurt, Germany "Chris" schrieb im Newsbeitrag ... I have a column with dates and another with totals. I want to for every month (01 - 12) to search all the rows in columnA and sum the totals only relevent to the month from columnB. E.g for OCtober the total is 1500 ColumnA columnB 10/10/04 1000 10/10/04 500 10/09/04 250 |
Here you go
=SUMPRODUCT(--(MONTH(C4:C6=10),--(YEAR(C4:C6=2004),E4:E6) COlumn C has the dates formated as date mm/dd/yy and column E has the amounts, formatted as currency £ Cheers "Frank Kabel" wrote: Hi just post the formula you have tried -- Regards Frank Kabel Frankfurt, Germany Chris wrote: For my sins I used what you said below (I am a novice at this) I used a range. I need to total all the amounts that have a month of say 04 or 10 associated. The error I get is the standard formula error. If that makes sense? "Frank Kabel" wrote: Hi what is the formula you have used and what exact error do you get. Note: you can't use a range A:A together with SUMPRODUCT. For an explanation see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany Chris wrote: Hi Frank this gives me an error. I've changed the A1:100 ranges to my one. Any ideas. Using 2002 version. Also what do the -- mean Cherers Chris "Frank Kabel" wrote: Hi try =SUMPRODUCT(--(MONTH(A1:A100=10),--(YEAR(A1:A100=2004),B1:B100) -- Regards Frank Kabel Frankfurt, Germany "Chris" schrieb im Newsbeitrag ... I have a column with dates and another with totals. I want to for every month (01 - 12) to search all the rows in columnA and sum the totals only relevent to the month from columnB. E.g for OCtober the total is 1500 ColumnA columnB 10/10/04 1000 10/10/04 500 10/09/04 250 |
Hi, Frank:
Maybe the problems is with missing parentheses: =SUMPRODUCT(--(MONTH(A1:A100)=10),--(YEAR(A1:A100)=2004),B1:B100) On Fri, 29 Oct 2004 18:16:05 +0200, "Frank Kabel" wrote: Hi try =SUMPRODUCT(--(MONTH(A1:A100=10),--(YEAR(A1:A100=2004),B1:B100) |
Hi
try: =SUMPRODUCT(--(MONTH(C4:C6)=10),--(YEAR(C4:C6)=2004),E4:E6) -- Regards Frank Kabel Frankfurt, Germany Chris wrote: Here you go =SUMPRODUCT(--(MONTH(C4:C6=10),--(YEAR(C4:C6=2004),E4:E6) COlumn C has the dates formated as date mm/dd/yy and column E has the amounts, formatted as currency £ Cheers "Frank Kabel" wrote: Hi just post the formula you have tried -- Regards Frank Kabel Frankfurt, Germany Chris wrote: For my sins I used what you said below (I am a novice at this) I used a range. I need to total all the amounts that have a month of say 04 or 10 associated. The error I get is the standard formula error. If that makes sense? "Frank Kabel" wrote: Hi what is the formula you have used and what exact error do you get. Note: you can't use a range A:A together with SUMPRODUCT. For an explanation see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany Chris wrote: Hi Frank this gives me an error. I've changed the A1:100 ranges to my one. Any ideas. Using 2002 version. Also what do the -- mean Cherers Chris "Frank Kabel" wrote: Hi try =SUMPRODUCT(--(MONTH(A1:A100=10),--(YEAR(A1:A100=2004),B1:B100) -- Regards Frank Kabel Frankfurt, Germany "Chris" schrieb im Newsbeitrag ... I have a column with dates and another with totals. I want to for every month (01 - 12) to search all the rows in columnA and sum the totals only relevent to the month from columnB. E.g for OCtober the total is 1500 ColumnA columnB 10/10/04 1000 10/10/04 500 10/09/04 250 |
Hi
:-) thought so after seeing the formula the OP tried Sorry for my typos -- Regards Frank Kabel Frankfurt, Germany Myrna Larson wrote: Hi, Frank: Maybe the problems is with missing parentheses: =SUMPRODUCT(--(MONTH(A1:A100)=10),--(YEAR(A1:A100)=2004),B1:B100) On Fri, 29 Oct 2004 18:16:05 +0200, "Frank Kabel" wrote: Hi try =SUMPRODUCT(--(MONTH(A1:A100=10),--(YEAR(A1:A100=2004),B1:B100) |
Nice one
Many thanks "Myrna Larson" wrote: Hi, Frank: Maybe the problems is with missing parentheses: =SUMPRODUCT(--(MONTH(A1:A100)=10),--(YEAR(A1:A100)=2004),B1:B100) On Fri, 29 Oct 2004 18:16:05 +0200, "Frank Kabel" wrote: Hi try =SUMPRODUCT(--(MONTH(A1:A100=10),--(YEAR(A1:A100=2004),B1:B100) |
One last thing, can I get this to go down a column until it meets a blank
cell rather than specify a range? "Frank Kabel" wrote: Hi :-) thought so after seeing the formula the OP tried Sorry for my typos -- Regards Frank Kabel Frankfurt, Germany Myrna Larson wrote: Hi, Frank: Maybe the problems is with missing parentheses: =SUMPRODUCT(--(MONTH(A1:A100)=10),--(YEAR(A1:A100)=2004),B1:B100) On Fri, 29 Oct 2004 18:16:05 +0200, "Frank Kabel" wrote: Hi try =SUMPRODUCT(--(MONTH(A1:A100=10),--(YEAR(A1:A100=2004),B1:B100) |
| All times are GMT +1. The time now is 07:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com