SUMPRODUCT query
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 |
SUMPRODUCT query
=SUMPRODUCT(--(Costs!$F$12:$F$57=$B7),--(MONTH(Costs!$H$12:$H$57)=1),--(Costs!$H$12:$H$57<""),Costs!$J$12:$J$57)
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "John" wrote in message ... 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 |
SUMPRODUCT query
Thanks Bob - did the trick.
"Bob Phillips" wrote: =SUMPRODUCT(--(Costs!$F$12:$F$57=$B7),--(MONTH(Costs!$H$12:$H$57)=1),--(Costs!$H$12:$H$57<""),Costs!$J$12:$J$57) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "John" wrote in message ... 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 |
SUMPRODUCT query
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 "John" wrote in message ... Thanks Bob - did the trick. "Bob Phillips" wrote: =SUMPRODUCT(--(Costs!$F$12:$F$57=$B7),--(MONTH(Costs!$H$12:$H$57)=1),--(Costs!$H$12:$H$57<""),Costs!$J$12:$J$57) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "John" wrote in message ... 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 |
All times are GMT +1. The time now is 03:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com