SUMPRODUCT with 3 criteria

[Kit]

I am not sure why the result always showing zero.
Column A contains name: A, B, C, A, C, ..etc
Column B contains range of date: MM/DD/YYY
Column C contains # of hours, some number are negatives

I am trying to find out the # of hours for name A in a range of date, so I
put the following equestion together, but it always return zero.
=SUMPRODUCT(--($A$5:$A$417="A"),--($B$5:$B$417>=39896),--($B$5:$B$417<=39909),($C$5:$C$417))

I am using MS 2003

thanks,
Kit

 

[Luke M]

It's working fine for me...are you sure you have serial dates entered into
column B, and not text? (try formatting the cells as number/general as a
check)

Or is it a weird fluke that the sum is actually zero?

 

[Elkar]

The formula looks fine to me. A couple things you might check. Are you sure
you're using the correct serial numbers for the dates you want to check in
column B? 39896 = March 24, 2009 and 39909 = April 6, 2009. Next, are your
numbers in column C entered as Text? Adding a -- to your final criteria will
take care of that. --($C$5:$C$417)

HTH
Elkar

 

[Kit]

I did look into the format of the column of the data. It seems like the
problem is there and no matter what I do, I can't seems to change the format.

I copied the data from a generated report and pasted to my excel workbook.
When I click on the format for the column with the dates. All format shows
example: 03/11/2009, even for General. The only way I can unlock the format
of the cell is to retype the date manually on the cell and the format will
correct itself. Is there anyway I can fix that problem?

Thanks,
Kit

 

[Elkar]

Often with imported data, the format will not be what you expect. Simply
changing the cell format will only apply to new data, not existing data. Try
applying "Text to Columns" to your column of data. Leave all the options as
default and click "Finish". This usually clears up any formatting problems.

HTH
Elkar

 

[Luke M]

You could use the DATEVALUE function then, like so:

=SUMPRODUCT(--($A$5:$A$417="A"),--(DATEVALUE($B$5:$B$417)>=39896),--(DATEVALUE($B$5:$B$417)<=39909),($C$5:$C$417))

DATEVALUE converts text that "looks" like a date into an actual serial date.

 

[Kit]

Thank Elkar,

Your method works

Kit


Your

Often with imported data, the format will not be what you expect. Simply
changing the cell format will only apply to new data, not existing data. Try
applying "Text to Columns" to your column of data. Leave all the options as
default and click "Finish". This usually clears up any formatting problems.

HTH
Elkar

 

[Kit]

Thanks for your help Luke.

Kit

You could use the DATEVALUE function then, like so:

=SUMPRODUCT(--($A$5:$A$417="A"),--(DATEVALUE($B$5:$B$417)>=39896),--(DATEVALUE($B$5:$B$417)<=39909),($C$5:$C$417))

DATEVALUE converts text that "looks" like a date into an actual serial date.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*