How do I sum hours with multiple criteria

[soconfused]

I have a spreadsheet that looks something like this:

W/C HOURS DATE
XEN1 .5 11/08/07
XDN5 1.8 12/01/09
XQAE 10 02/06/08
XEN1 1.7 03/25/09
XEN1 2.4 11/27/07
XDN5 5.9 11/30/09

I have to get the hours for all the XEN1 for the month/year for example:

I would need to arrive at 2.9 hours for XEN1 for the 11th month of 2007.

 

[Mike H]

Hi,

Try this

=SUMPRODUCT((A1:A10="XEN1")*(MONTH(C1:C10)=11)*(YEAR(C1:C10)=2007)*(B1:B10))

You should also format as [hh]:mm

Mike

 

[Fred Smith]

You should also format as [hh]:mm
Perhaps not. My bet is that the hours are stored as a number, not as a time.
If so, use any number format, or General.

Regards,
Fred

Hi,

Try this

=SUMPRODUCT((A1:A10="XEN1")*(MONTH(C1:C10)=11)*(YEAR(C1:C10)=2007)*(B1:B10))

You should also format as [hh]:mm

Mike

I have a spreadsheet that looks something like this:

W/C HOURS DATE
XEN1 .5 11/08/07
XDN5 1.8 12/01/09
XQAE 10 02/06/08
XEN1 1.7 03/25/09
XEN1 2.4 11/27/07
XDN5 5.9 11/30/09

I have to get the hours for all the XEN1 for the month/year for example:

I would need to arrive at 2.9 hours for XEN1 for the 11th month of 2007.

 

[soconfused]

That was great, but as I looked at my spreadsheet, I need to make the "XEN1"
a wildcard or be able to include two or three others such as XTEN AND XIEN
AND XQAE? Can that be done?

Thank you so much.
--
DMM

Hi,

Try this

=SUMPRODUCT((A1:A10="XEN1")*(MONTH(C1:C10)=11)*(YEAR(C1:C10)=2007)*(B1:B10))

You should also format as [hh]:mm

Mike

I have a spreadsheet that looks something like this:

W/C HOURS DATE
XEN1 .5 11/08/07
XDN5 1.8 12/01/09
XQAE 10 02/06/08
XEN1 1.7 03/25/09
XEN1 2.4 11/27/07
XDN5 5.9 11/30/09

I have to get the hours for all the XEN1 for the month/year for example:

I would need to arrive at 2.9 hours for XEN1 for the 11th month of 2007.