Sum Lookup from a 2nd table - SumIf and SumProduct(?)

Hi all

I'm trying to do a calendar for a shift pattern. I've a lookup table (held on the Shift times sheet) in the example file. I enter a shift agaisnt a persons name on a date, like S1 and S2.

For some sanity checking, i want to do a summary of the times per product (B3:B11) - there are 4 products, mulitple people agaisnt each product.

So C15 for example, i would be looking for the result 15 which it gets from looking at B3:B11 to find which are 'Func' and then pulling the corrisponding values that lookup from C3:C11 from the lookup sheet in A2:B10. So it comes up with 15 because it finds S3 = 7.5 and S1= 7.5 thus 15.

I dont want to add individual columns per date to perform the lookup value first and then sum on those as that would mean an extra 30 columns and having to hide them etc, i'd just like to do it in a more complex formula.
I've been banging my head on this for over 6 hours, it must be possible it's just getting my head around the logic. I believed it was a SUMPRODUCT(SUMIF( etc. but it might need an index/match and those are beyond my excel skills at the mo!

Any help would be appreciate thanks!

Andrew

Nope. Can't visualise all that stuff...


Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Try using this formula in C14 copied across and down as required

=SUMPRODUCT(($B$3:$B$11=$B14)+0,SUMIF('Shift Times'!$A$3:$A$10,C$3:C$11,'Shift Times'!$B$3:$B$10))