Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sumproduct to add total amounts for the month
I used the formula for adding number of entries, but now I need to change it
to add the amounts that were collected during each month: =SUMPRODUCT(--(MONTH(Base!$A$2:$A$1368)=9), --ISNUMBER(Lisan!$D$2:$D$1368)) A B C D (date) (Status) (Due) (Paid) 9/2/04 30.00 40.00 9/3/04 25.00 25.00 10/1/04 15.00 12.00 Total for column D for September $65.00 How can I change the formula to add the amounts paid per month? Thanks! Annette |
#2
|
|||
|
|||
=SUMPRODUCT(--(MONTH(Base!$A$2:$A$1368)=9), --ISNUMBER(Lisan!$D$2:$D$1368),B
ase!$D$2:$D$1368) -- HTH RP (remove nothere from the email address if mailing direct) "Annette" wrote in message ... I used the formula for adding number of entries, but now I need to change it to add the amounts that were collected during each month: =SUMPRODUCT(--(MONTH(Base!$A$2:$A$1368)=9), --ISNUMBER(Lisan!$D$2:$D$1368)) A B C D (date) (Status) (Due) (Paid) 9/2/04 30.00 40.00 9/3/04 25.00 25.00 10/1/04 15.00 12.00 Total for column D for September $65.00 How can I change the formula to add the amounts paid per month? Thanks! Annette |
#3
|
|||
|
|||
How about:
=SUMPRODUCT( MONTH(Base!$A$2:$A$1368) = 9 , Lisan!$D$2:$D$1368 ) ? "Annette" schreef in bericht ... I used the formula for adding number of entries, but now I need to change it to add the amounts that were collected during each month: =SUMPRODUCT(--(MONTH(Base!$A$2:$A$1368)=9), --ISNUMBER(Lisan!$D$2:$D$1368)) A B C D (date) (Status) (Due) (Paid) 9/2/04 30.00 40.00 9/3/04 25.00 25.00 10/1/04 15.00 12.00 Total for column D for September $65.00 How can I change the formula to add the amounts paid per month? Thanks! Annette |
#4
|
|||
|
|||
Thanks, Bob ... perfect solution!
"Bob Phillips" wrote in message ... =SUMPRODUCT(--(MONTH(Base!$A$2:$A$1368)=9), --ISNUMBER(Lisan!$D$2:$D$1368),B ase!$D$2:$D$1368) -- HTH RP (remove nothere from the email address if mailing direct) "Annette" wrote in message ... I used the formula for adding number of entries, but now I need to change it to add the amounts that were collected during each month: =SUMPRODUCT(--(MONTH(Base!$A$2:$A$1368)=9), --ISNUMBER(Lisan!$D$2:$D$1368)) A B C D (date) (Status) (Due) (Paid) 9/2/04 30.00 40.00 9/3/04 25.00 25.00 10/1/04 15.00 12.00 Total for column D for September $65.00 How can I change the formula to add the amounts paid per month? Thanks! Annette |
#5
|
|||
|
|||
Hi Johannes
:-) this as well needs the double minus :-))) =SUMPRODUCT(--(MONTH(Base!$A$2:$A$1368) = 9) , Lisan!$D$2:$D$1368) -- Regards Frank Kabel Frankfurt, Germany "Johannes" schrieb im Newsbeitrag ... How about: =SUMPRODUCT( MONTH(Base!$A$2:$A$1368) = 9 , Lisan!$D$2:$D$1368 ) ? "Annette" schreef in bericht ... I used the formula for adding number of entries, but now I need to change it to add the amounts that were collected during each month: =SUMPRODUCT(--(MONTH(Base!$A$2:$A$1368)=9), --ISNUMBER(Lisan!$D$2:$D$13 68)) A B C D (date) (Status) (Due) (Paid) 9/2/04 30.00 40.00 9/3/04 25.00 25.00 10/1/04 15.00 12.00 Total for column D for September $65.00 How can I change the formula to add the amounts paid per month? Thanks! Annette |
#6
|
|||
|
|||
Yep, guessed that one... only is with sumproduct rigth?
"Frank Kabel" schreef in bericht ... Hi Johannes :-) this as well needs the double minus :-))) =SUMPRODUCT(--(MONTH(Base!$A$2:$A$1368) = 9) , Lisan!$D$2:$D$1368) -- Regards Frank Kabel Frankfurt, Germany "Johannes" schrieb im Newsbeitrag ... How about: =SUMPRODUCT( MONTH(Base!$A$2:$A$1368) = 9 , Lisan!$D$2:$D$1368 ) ? "Annette" schreef in bericht ... I used the formula for adding number of entries, but now I need to change it to add the amounts that were collected during each month: =SUMPRODUCT(--(MONTH(Base!$A$2:$A$1368)=9), --ISNUMBER(Lisan!$D$2:$D$13 68)) A B C D (date) (Status) (Due) (Paid) 9/2/04 30.00 40.00 9/3/04 25.00 25.00 10/1/04 15.00 12.00 Total for column D for September $65.00 How can I change the formula to add the amounts paid per month? Thanks! Annette |
#7
|
|||
|
|||
Hi
the double minus is used as ONE possible mathematical calculation to coerce a boolean value. Instead of this you could use 0+(...) 1*(...) (..)^1 N(...) where (...) is a boolean expression Performance difference is not really an issue though my testing showed that -- is about 5 - 30% faster than the other ones (N(...) the second fastest and (...)^1 the slowest one). so don't use that option :-) You could also use this to convert numeric values stored as Text to real numbers (a substitute for the VALUE function) -- Regards Frank Kabel Frankfurt, Germany "Johannes" schrieb im Newsbeitrag ... Yep, guessed that one... only is with sumproduct rigth? "Frank Kabel" schreef in bericht ... Hi Johannes :-) this as well needs the double minus :-))) =SUMPRODUCT(--(MONTH(Base!$A$2:$A$1368) = 9) , Lisan!$D$2:$D$1368) -- Regards Frank Kabel Frankfurt, Germany "Johannes" schrieb im Newsbeitrag ... How about: =SUMPRODUCT( MONTH(Base!$A$2:$A$1368) = 9 , Lisan!$D$2:$D$1368 ) ? "Annette" schreef in bericht ... I used the formula for adding number of entries, but now I need to change it to add the amounts that were collected during each month: =SUMPRODUCT(--(MONTH(Base!$A$2:$A$1368)=9), --ISNUMBER(Lisan!$D$2:$D$13 68)) A B C D (date) (Status) (Due) (Paid) 9/2/04 30.00 40.00 9/3/04 25.00 25.00 10/1/04 15.00 12.00 Total for column D for September $65.00 How can I change the formula to add the amounts paid per month? Thanks! Annette |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to extract month number from month name | Excel Discussion (Misc queries) | |||
Total number of each month in a column of dates | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |