Challenge: Vlook-up/match with a sumif

**shanea.kr** · 05-31-2012, 03:10 PM

Ok so I have a challenging and complex spreadsheet formula. I have this formula and others where the date and the username changes. =SUMIFS(S1T1!E:E,S1T1!A:A,B2,S1T1!A:A,C2,S1T1!B:B,"Pick Item to WO",S1T1!C:C,"TULBRAZC").

I want to add the ability for when I type in the username in column b for it to match the values for that username from s1t1 and insert it in to the equation. This way if a person were to move shifts, all of the equations would calculate with emphasis on making sure the productivity works

Copy of mike new spreadsheet-MASTER.xlsx

**shanea.kr** · 05-31-2012, 03:12 PM

This is the correct spreadsheet

Copy of mike new spreadsheet-MASTER.xlsx

**NBVC** · 05-31-2012, 03:16 PM

Use some Absolute referencing and direct references to cells containing the search strings...

e.g. in C5:

=SUMIFS(S1T1!$E:$E,S1T1!$A:$A,$B$2,S1T1!$A:$A,$C$2,S1T1!$B:$B,C$4,S1T1!$C:$C,$B5)

you can copy formula across.

Then copy that row and paste to the other rows needing the formula... the references will self update.