Solved: Sumproduct problem? Driving me mad!

[Sir Babydum GBE]

I have a tricky dilemma, which I just can?t solve ? I?m hoping that your expertise will come to my rescue!

On the attached example workbook, I am trying to track what staff are planned to do what project ? and whether they actually did it or not.
The idea of the spreadsheet is that:

1. The user looks at "Project Plan" and decides (for example) that he needs 2 tennis players on 2nd Jan 2006. So in the "Tennis" section of that page, under "Req", he inputs 2 on the correct row (according to the date).
2. Now he looks at the "Worker Plan" sheet. And will select a blank cell under one of his team and allocate them to tennis ("ten") ? i have done this for two staff.
3. Now, in "project plan" my Sumproduct formula will return the number of staff planned for each project on each day.
4. So far so good. But here?s where the tricky stuff starts
5. When the days pass ? the staff are expected to put a code into the "worker actual" sheet if they weren?t able to do their assigned project that day. So I mark one person off as being on holiday/vacation

What I now need the "project plan" to calculate is how many people actually were available on the day for each project. This is where i?m stuck.

As the "people actual" sheet doesn?t contain details of the project ? only the reason for absence ? how to I get a formula to cross-check what a person is working on with his reason for absence so as to deduct 1 from the total of people available for the project on "project plan"

In other words ? look at the 3 sheets, then look at "project plan". In the red cell a formula should return a 1. Because there were 2 people assigned to that project, but one of those people has an entry in the "person actual" sheet ? which means he wasn?t available that day. (Any entry in this sheet will be taken to mean that the person was unavailable that day ? otherwise it is left blank).

Please help! I?m frying my brain and Harrops don?t do Excel books.

[Sir Babydum GBE]

Oh - I just worked it out with an array formula:

{=D5-(IF('Worker Plan'!C5:E5=C3,COUNTA('Worker Actual'!C5:E5),0))}

In the red cell. Hmm - this is the first ever array I've used.

Excel help files rule!

[Sir Babydum GBE]

Blast - I lied - can someone unsolve this for me please?

That formula will count anything that has a value regardless of whether the corresponding value on the other sheet is correct.

I must be getting closer though - anyone good at arrays?

[Sir Babydum GBE]

Right - this seems to do the trick:

{=D5-SUM(('Worker Plan'!$C5:$E5=C3)*('Worker Actual'!$C5:$E5>0))}

Reluctant to mark thread as solved after last time - would someone mind checking the formula to make sure i'm not overlooking something silly.

Thanks

[Bob Phillips]

You could also use the non-array

=SUMPRODUCT(('Worker Plan'!$B$5:$B$14=$B5)*
('Worker Plan'!5:5=C$3)*
('Worker Actual'!5:5=""))

which is also more self-auditing as it is not dependent on other calculations.

[Sir Babydum GBE]

You could also use the non-array

=SUMPRODUCT(('Worker Plan'!$B$5:$B$14=$B5)*
('Worker Plan'!5:5=C$3)*
('Worker Actual'!5:5=""))

which is also more self-auditing as it is not dependent on other calculations.

Thanks Mr X - i think that is probably the better option of the two! Good to hear from you again...