SUMPRODUCT query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John - 18 Mar 2008 21:38 GMT

Hi

I'm using the following sumproduct:
=SUMPRODUCT(--(Costs!$F$12:$F$57=$B7),--(MONTH(Costs!$H$12:$H$57)=1),Costs!$J$12:$J$57)

Costs!H12:H57 is a list of dates - on occassion this may be blank but there
still be a value in the Costs!J12:J57 column. For some reason, if the date
field is blank then the value is included in the calculation (as though there
was a January date in the date field).

I want it to ignore the value if the date field is blank - anyone got any
ideas? (I obviously have a column for each month (MONTH= 1 through 12))

Thanks

Reply to this Message

Bob Phillips - 18 Mar 2008 21:50 GMT

=SUMPRODUCT(--(Costs!$F$12:$F$57=$B7),--(MONTH(Costs!$H$12:$H$57)=1),--(Costs!$H$12:$H$57<>""),Costs!$J$12:$J$57)

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi
>
[quoted text clipped - 12 lines]
>
> Thanks

Reply to this Message

John - 18 Mar 2008 22:19 GMT

Thanks Bob - did the trick.

> =SUMPRODUCT(--(Costs!$F$12:$F$57=$B7),--(MONTH(Costs!$H$12:$H$57)=1),--(Costs!$H$12:$H$57<>""),Costs!$J$12:$J$57)
>
[quoted text clipped - 14 lines]
> >
> > Thanks

Reply to this Message

Tyro - 18 Mar 2008 23:57 GMT

If cell A1 is blank, MONTH(A1) returns 1 which is January. The reason is
that Excel's dates are actually numbers. Excel's date range is from Jan. 1,
1900, day 1 through Dec. 31, 9999, day 2,958,465. March 18, 2008 is day
39,525. If you format that number as mm/dd/yy you will see 03/18/08.
However, there is a fictious date in Excel of Jan. 0, 1900 and its number is
0. So, MONTH(blank cell) is the same as MONTH(0) and returns a 1, the month
number of Jan. 0, 1900.

Tyro

> Thanks Bob - did the trick.
>
[quoted text clipped - 18 lines]
>> >
>> > Thanks

Reply to this Message