Calculating a sum of many IF(INDEX) functions

**BHammy** · 09-04-2012, 02:07 PM

Hi all,

I know how to calculate a big sum using the first and last item, like =SUM(E1:E300)

Is there a similar way to calculate a big sum of IF(INDEX) functions that are aligned in a predictable pattern, without typing each one individually?

One item would be =IF($A$1<>0,INDEX(Sheet1!$B:W,Sheet2!$A$1,Sheet2!$K$1,0)
Two items would be =SUM(IF($A1<>0,INDEX(Sheet1!$B:$W,Sheet2!$A1,Sheet2!$K$1,0)
+ IF($A2<>0,INDEX(Sheet1!$B:$W,Sheet2!$A2,Sheet2!$K$1,0)

Three items would be the above plus the next item would have A3 instead of A1 or A2.

I would like a function that sums 3000 of the items, so that I only have to type in the first and last. Does such a function exist?

For example:

=A SUM FUNCTION I DON'T KNOW(IF($A1<>0,INDEX(Sheet1!$B:W,Sheet2!$A1,Sheet2!$K$1,0):IF($A3000<>0,INDEX(Sheet1!$B:W,Sheet2!$A3000,Sheet2!$K$1,0)

Thanks!

**daddylonglegs** · 09-04-2012, 03:19 PM

I'm not sure what the zero is doing at the end of the INDEX function, I wouldn't expect to see that there, does that work?

You should be able to do this but with a slightly different formula. Also rather than using the whole range B:W I would restrict that to a specific number of rows for efficiency purposes, e.g. assuming up to 100 rows (adjust as required) try this "array formula"

=SUM(IF(ROW(Sheet1!B1:W100)-ROW(Sheet1!B1)+1=TRANSPOSE(Sheet2!A1:A3000),INDEX(Sheet1!B1:W100,0,Sheet2!K1)))

confirmed with CTRL+SHIFT+ENTER