Home 
Search 
Today's Posts 
#1




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 
#2




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 
#3




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 
#4




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 
#5




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 
#6




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 
#7




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 
#8




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) 
#9




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 
#10




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) 
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
How to extract month number from month name  Excel Discussion (Misc queries)  
Displaying value of specific cell within a range, with IF function...?  Excel Discussion (Misc queries)  
what formula do i put for column m = column k minus column l in e.  Excel Discussion (Misc queries)  
How do I apply a Function to a column of cells and change there va  New Users to Excel  
SUMIF(AND) FUNCTION  Excel Worksheet Functions 