[simer]

Here is my challenge.

I have a table representing Demand employees on various dates for "AM" shifts, and "PM" shifts.

23/1/10 24/1/10 25/1/10 26/1/10 27/1/10
Demand AM 5 5 5 5 15
Demand PM 5 5 5 5 15

Below this I have a list of names followed by the shift they are working, and the date.

Volunteer Name Shift Shift Date
Frank PM 23/01/2010
Allice AM 24/01/2010
Brandon PM 25/01/2010
Craig AM 26/01/2010

What I need is a formula that will subtract 1 from the demand total of either Demand AM or Demand PM when "AM" or "PM" is entered in these cells corresponding to the date. Any ideas? Help me!

[bomb #21]

If Excel 2007, use SUMIFS.

Otherwise, use something like:

=B3-SUMPRODUCT(($B$8:$B$12="PM")*($C$8:$C$12=$B$2))