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

=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

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

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

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

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

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