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.

 

[smartin]

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.

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

You can modify Mike's formula a little to look for the search term in a
cell.

Suppose column F has XEN1, XTEN, etc.

Then use this, e.g., in G1:

=SUMPRODUCT(($A$1:$A$10=F1)*(MONTH($C$1:$C$10)=11)*(YEAR($C$1:$C$10)=2007)*($B$1:$B$10))

Notice I also added absolute referencing for the data area, so you can
fill this formula down as far as needed.

Using the "cell as a search term" technique, you can also make variables
out of the year and month.