Sumproduct

[BuzzG]

Is there a way to sum values with the sumproduct function that uses multiple criteria from one column. EX. =SUMPRODUCT((Employee=1001)*(Workcatagory="H")*Hours). I need to sum the hours with Workcatagory equal to "H" AND "W". I can't seem to get the right syntax. Is this possible in one formula
BuzzG

 

[BuzzG]

I need to sum the hours using 2 values for workcatagory - "H" and "W". Any thoughts?

 

[Aladin Akyurek]

On 2002-09-20 13:16, BuzzG wrote:
Is there a way to sum values with the sumproduct function that uses multiple criteria from one column. EX. =SUMPRODUCT((Employee=1001)*(Workcatagory="H")*Hours). I need to sum the hours with Workcatagory equal to "H" AND "W". I can't seem to get the right syntax. Is this possible in one formula
BuzzG

=SUMPRODUCT((Employee=D1)*(Workcatagory=E1:F1)*Hours)

where D1 houses an employee number like 1001 and E1:F1 "H" and "W" (without double quotes).

 

[BuzzG]

Thanks Aladin for tip. It works great!

 

[Scott R]

Try this:
=SUMPRODUCT((Employee=1001)*((WorkCategory="H")+(WorkCategory="W")),Hours)

 

[BuzzG]

Scott, that method works also. Two great answers.
BuzzG