Vlookup, Indirect, Sum of Multiple Pages in excel... Can you do this?

[leighnea]

I have a payroll report that I extract from ADP. It doesn't extract on the exact same line everyday it depends on who is working.

I have wrote the formula for looking up the day total and it works however the MTD total is what I can't figure out.

It needs to sum every time it finds a title - my formula currently is =SUM((VLOOKUP($K6,INDIRECT("'"&$B$1&"'!$d$1:$p$3000"),2,FALSE))) which is not summing all days only the day I am running the report for.

 

[platonic567]

VLOOKUP returns a single value hence no point in combining with SUM.

You can try my below formula:

=SUMPRODUCT(--($K6=INDIRECT("'"&$B$1&"'!$D$1:$D$3000")),INDIRECT("'"&$B$1&"'!$E$1:$E$3000"))

 

[Andrew Poulsom]

You can use SUMPRODUCT(SUMIF as in Post #2 here:

http://www.mrexcel.com/forum/excel-questions/119020-sumif-multiple-sheets.html

 

[leighnea]

THANK YOU MR.EXCEL!! It worked! =SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!d2:d100"),D11,INDIRECT("'"&list&"'!e2:e100")))

Now only one thing this formula sums all 31 worksheets not just the MTD. Any one know what I need to add?