I can't find the right mix of formulas, sumifs, match, sumproduct

I have a large table of data and need to look up the department in one column, the job type in another column and then add the costs related to those two criteria. However the costs are in columns by month and I would like the formula to be able to add costs for the months to date indicated by the date given.


Each job type will be on a separate line and i'd like to be able to change the YTD number. So I can see for the department "Tower" as of 3/31/16, the total costs are $20,360 for X-RAY and $15,705 for Ultrasound. So the values in Cells C21 & C22 will change if I change the date in C20.

this is helpful...almost there....can we add something to lookup cell 20 in column A similar to looking up cell 21 in column F??
THANK YOU!!

Or try this ...

=SUMPRODUCT(($F$3:$F$15=A21)*OFFSET($A$3:$A$15,0,1,,MATCH($C$20,$B$1:$E$1,0)))

Normal enter.

Thank you for this answer! I would like to add one thing if possible. Is there a way to include the Department as a lookup condition? I'd like the formula to look up the department listed in A20 in column A. There can be the same Job Type for multiple departments so it's important to be able to add that. Thank you!

Try ...
=SUMPRODUCT(($A$3:$A$15=$A$20)*($F$3:$F$15=A21)*OFFSET($A$3:$A$15,0,1,,MATCH($C$20,$B$1:$E$1,0)))

Ok, nevermind, I was able to add the piece I needed myself. THANK YOU SO MUCH!! I appreciate your help! I had been working at this for so long! I need to learn more about what offset does.

You're welcome!