Question on index

[Paul B]

How does this sum sheet1 A1 to the last thing in column A without putting a
number or foumula in D1? =SUM(Sheet1!A1:INDEX(Sheet1!A:A,$D$1))



I have use this in D1, =MATCH(9.99999999999999E+307,Sheet1!A:A)

, before to use this kind of formula but today I put the formula in without
anything in D1 and it looks like it works????



Is this the best way to do this??



Thanks, using excel 2003

 

[JE McGimpsey]

Start here:

From XL Help ("INDEX"):

If you set row_num or column_num to 0 (zero),
INDEX returns the reference for the entire column
or row, respectively

 

[Shane Devenshire]

Hi,

If you don't specify the second argument is assumes all, so you could
actually replace

=SUM(Sheet1!A1:INDEX(Sheet1!A:A,$D$1))

with

=SUM(Sheet1!A1:INDEX(Sheet1!A:A,))

And it would still work. However, this defeats the purpose of the formula,
which is to control how many cells are summed. If you just want to sum them
all, this formula is overkill, you should just use =SUM(A:A)

 

[Paul B]

I was trying to incorporate it in this formula to get the last row, is there
another way to do it with a formula like this?



SUMPRODUCT((Data!$A$3:$A$579=$B52)*(YEAR(Data!$B$3:$B$579)=$A52),Data!F$3:F$579)



Column A is names

Column B is dates

Column F is price



Thanks