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 (D4:D233) 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)

"David Lipetz" <[email protected]> wrote in message
news:[email protected]...
> I just can't seem to figure out which formula to use to make this work.
>
> I've got invoice dates in column D (D4:D233) 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.
>
>

[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 wrote:
>
> I just can't seem to figure out which formula to use to make this work.
>
> I've got invoice dates in column D (D4:D233) 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.

--

Dave Peterson

[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 --.

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> =SUMPRODUCT(--(MONTH($D$4:$D$223)=1),$E$4:$E$233)
>
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "David Lipetz" <[email protected]> wrote in message
> news:[email protected]...
>> I just can't seem to figure out which formula to use to make this work.
>>
>> I've got invoice dates in column D (D4:D233) 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.
>>
>>
>
>

[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


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
>>
>> I just can't seem to figure out which formula to use to make this work.
>>
>> I've got invoice dates in column D (D4:D233) 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.
>
> --
>
> Dave Peterson

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

"David Lipetz" <[email protected]> wrote in message
news:[email protected]...
> 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 --.
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > =SUMPRODUCT(--(MONTH($D$4:$D$223)=1),$E$4:$E$233)
> >
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from the email address if mailing direct)
> >
> > "David Lipetz" <[email protected]> wrote in message
> > news:[email protected]...
> >> I just can't seem to figure out which formula to use to make this work.
> >>
> >> I've got invoice dates in column D (D4:D233) 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.
> >>
> >>
> >
> >
>
>

[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.

David Lipetz wrote:
>
> 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 --.
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > =SUMPRODUCT(--(MONTH($D$4:$D$223)=1),$E$4:$E$233)
> >
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from the email address if mailing direct)
> >
> > "David Lipetz" <[email protected]> wrote in message
> > news:[email protected]...
> >> I just can't seem to figure out which formula to use to make this work.
> >>
> >> I've got invoice dates in column D (D4:D233) 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.
> >>
> >>
> >
> >

--

Dave Peterson