Another Date Related Formula Question

[David Lipetz]

I just can't seem to figure out which formula to use to make this work.

I've got invoice dates in column D (D4233) and Invoice Amounts in column E
(E4:E233).

I need to do two things: count the number of invoices in each month, and sum
the invoices for each month.

I was able to get the count formula correct, as an example
for January:
=SUMPRODUCT((MONTH($D$4:$D$223)=1)*1)

But can not figure out how to SUM column E (invoice amounts) for each given
month.

Your assistance is appreciated.

 

[Bob Phillips]

=SUMPRODUCT(--(MONTH($D$4:$D$223)=1),$E$4:$E$233)


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

If you have empty cells in D4:d233, you may want to use:

=SUMPRODUCT(--(MONTH($D$4:$D$233)=1),--(ISNUMBER($D$4:$D$233)),($E$4:$E$233))

(when the cell is empty, =month() will return 1)

 

[David Lipetz]

Thanks Bob. This works great.

Do I understand this correctly:

--(MONTH($D$4:$D$223)=1): this part of the formula checks the invoice dates
for those invoices dated in January, the -- turns the TRUEs in to 1s.

The rest of the formula simply sums the invoice amounts for the selected
invoices and the SUMPRODUCT multiplies the first sum (1) with the second sum
for our result.

What I really don't understand though is why the Month() statement returns
TRUE rather than a number, forcing us to use the --.

 

[David Lipetz]

Thanks Dave. Yours also wortks of course and adds error checking. I
responded to Bob's post trying to get an understanding of the logic behind
this so i don't have to rely on you good folks all the time.

Thanks,
David

 

[Bob Phillips]

You have basically got it right. The TRUE is because it is testing the month
of each date against a value, 1, which evaluates to TRUE or FALSE. Just
enter =MONTH(TODAY())=1 in a cell, and see what I mean.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

It's not the =month() portion that's producting the true/false.

It's the comparison:

Put Jan 1, 2006 in A1.
Put =month(a1) in b1. You'll see 1.
put =month(a1)=1 in c1. You'll see True.
It's essentially the same as:
=if(month(a1)=1,TRUE,FALSE)

Then put --(month(a1)=1) in D1 and you'll see 1.

Change the date in A1 and you'll see falses change to 0's.