Struggling with Vlookups

[DP17]

I posted before but struggling with this site too lol.

Trying to get column O to add up all the sheets. The present formula includes in cell O15 includes one sheet.

Thanks,

Dale

[FDibbins]

try this, copied down...
=SUM('101:125'!O15)

[JBeaucaire]

1) On the Facilities sheet, in column S starting at S1, list the sheet names that you want to sum.

S1: 101
S2: 102
.
.
.
.
S14: 125

2) In cell O15, enter this array formula:

=SUM(SUMIF(INDIRECT($S$1:$S$14&"!B:B"), B15, INDIRECT($S$1:$S$14&"!O:O")))

...confirm it by pressing CTRL-SHIFT-ENTER to activate the array. You will see curly braces { } appear around your formula to indicate the array is active and the first value of 13,000 should appear.

3) Copy O15 down the column O as needed.

[DP17]

This would work but doesn't as the categories in column B are staggered so a VLookup of some kind is needed?

[DP17]

JBeaucaire, I tried it and it just did not work for me.

Thanks for trying,

Dale

[custom63willys]

You can do it the low budget way: =VLOOKUP($B16,'101'!B2:P133,14,)+VLOOKUP($B16,'102'!B2:P133,14,)+VLOOKUP($B16,'103'!B2:P133,14,)+... or SUM(vlookup($B16,'101'!B2:P133,14,),VLOOKUP($B16,'102'!B2:P133,14,),....)

[DP17]

63Willys, Neither worked for me. Guess I am on the verge of giving up.

Dale

[vlady]

some way same as JB with sumproduct

CAPEX REPORT AUG 2012 1 Final dp (1).xls

[JBeaucaire]

I don't see what the problem is. Here's your file back with those exact steps installed from post #3.

[DP17]

Looks all good to me. I never used the "outer parenthesis"{} and plus (stupid mistake) I put S1:101 etc. into cell S1 on down lol

Thanks,

DP

[JBeaucaire]

If that takes care of your need, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

[DP17]

Thanks all