SUMPRODUCT - Multiple Criteria

S

soulstate

Joined
Mar 19, 2012
Messages
2
Reaction score
0
Hi,

I am really struggling to find an answer. I am creating a Dashboard and have the following data on the Data sheet:

Manager (A)
Month (B)
Year (D)
Sickness Hours (G)

On the Dashboard sheet I have the same

Manager (C)
Year (D4)
Sickness Hours

But above I have a Validation List with the Year in. What I am trying to do is get it to search by whatever year and by the manager to calculate the total cost of hours. Here is what I have tried:

=SUMPRODUCT(--((Data!A4:A15=C10)+(Data!E4:E15=D4)),--(Data!G4:G15))

But this just calculates all hours by said manager. Any help would be great. Using Excel 2003
 
Ok so I tried it this way round

=SUMPRODUCT(--(Data!G4:G15),--(Data!A4:A15=C10),--(Data!D4:D15="2012"))

And it works but that's because I have put ="2012" instead of the cell reference for the validation list. Any ideas? I want it to be dynamic so typing 2012 isn't an option. The year field on the Data sheet is a formula that takes the year from the month and year like so:

=TEXT($B7, "yyyy")

Does this make a difference as if I try it this way:

=SUMPRODUCT(--(Data!$A$4:$A$2000=C10)*(Data!$D$4:$D$2000=D4)*(Data!$G$4:$G$2000))

But manually type 2012 on the data sheet rather than using the text formula it works fine.
 
Back
Top