SUMPRODUCT or COUNTIF issues

Hi All,

I recently posted regarding countifs / sumproducts and got some good answers back. The previous thread is here and gives some context to my question below.

http://www.excelforum.com/excel-form...ta-ranges.html

It's become a bit more complex on me.

I am formulating a roster for a large workshop and need to be able to calculate how many hours are available for specific groups of tradespeople on specific days. For example, I need to know how many electricians hours are available to work on a particular day.

The top Rows are days of the week (Mon-Fri)
The columns are sorted by type of trade and name.

I need to firstly filter by the type of trade (electrician, fitter etc). This is in range A12:A120.

Then I need to count the shift type they are on. For example "D", "R" and "N" in range say HY12:HY120

*If they are an "electrician" and a "D" then multiply by 7.1 hours for that day.
*If they are an "electrician" and an "R" then multiply by 10.1 hours for that day.
*If they are an "electrician and an "N" then multiply by 10.1 hours for that day.

So if D appears say 5 times in the range AND they are also an electrician, then it would be 5*7.1. This would give me total work hours for that day.

I'll then need to do the same for other trades

At the moment I have the below formula, but its only giving me hours for Electricians on Day shift.

=sumproduct((HY12:HY120="D")*(A12:A120="Electricians"))*7.1

How do I also factor in the Electricians on shift type "R"?

I've linked them to actual cells not manually written it in.

I hope this explains what I mean.

Would appreciate if anyone can assist.

Cheers
Alex

gday all,

Ignore the above,

I've just solved it using an extended version of the sumproduct formula I used previously.

All good,

Cheers
Alex