unknown range

[dyc428]

If I want to sum up #'s of a column but the number of values change.

how can I count up the number of #'s in the column so i can replace * with the count?

SUM(A1:A*)

* is how many numbers are in the column

 

[Jon von der Heyden]

Hi, welcome to the board!

Will you have any blank cells in the column?

If not I think you could use:
=SUM(OFFSET($A$2,0,0,COUNTA($A$2:$A$65536),1))

* This assumes A1 contains a label....

Good luck

 

[fairwinds]

Hi,

Try:

=SUM(A1:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))

 

[Jon von der Heyden]

Fairwinds, what's the +307 do? Seen this a few times (I think) but still haven't grasped the concept yet??

 

[fairwinds]

It is just how you show a big number. I think it is called scientific notation in english.

9.99999999999999E+307
is actually the largest number you manually can enter in excel.

 

[Jon von der Heyden]

Cool, that's worth remembering.

Can I be a pain and ask why this formula works? From my perspective this would be the same as =SUM(A1:26) *in my example*, yet it works perfectly?

I.e. in my example the INDEX(MATCH()) returns 26 (thats how many rows of numbers I have)...?

 

[fairwinds]

When you use a formula like LOOKUP, VLOOKUP with last condition TRUE or MATCH with last condition 1 or omitted, i.e. funktions that looks for the largest value less than or equat to lookup value in a sorted list, you will, if you use a lookup value larger than any value in the range, get the last numerical value as return.

 

[Jon von der Heyden]

Sorry Fairwinds I'm still lost, I get the concept of the lookup finding the last value by using the higest number, but I'm stuck on why it returns =SUM($A$1:$A$27)

Book3
	A	B	C	D
1	Number #
2	1		Formula	26
3	1
4	1
5	1
6	1
7	1
8	1
9	1
10	1
11	1
12	1
13	1
14	1
15	1
16	1
17	1
18	1
19	1
20	1
21	1
22	1
23	1
24	1
25	1
26	1
27	1
Sheet1

This is the outcome if I evaluate the formula:
E1: =SUM(A1:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))

E2: =SUM($A$1:INDEX($A:$A,27))
(q) why do the references become fixed ($)?

E3: =SUM($A$1:$A$27)
(q) INDEX($A:$A,27) returns a value of 1, why does it create cell reference $A$27?

I would love to understand the logic behind this, far more useful than using the COUNTA() since COUNTA() is constrained by empty cells within the range.

Thanks.

 

[fairwinds]

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

does actually not return a value when used in a formula like this. It returns a range, in this case A27. (If you put the formula in the sheet it will return the contents of that range i.e. 1).


So in this case the formula should be evaluated as:

=SUM(A1:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))

=SUM(A1:A27)

 

[dyc428]

Thanks for the help guys,

I want to learn about writing macros. I was wondering if anyone knows a beginner book I can get that has plenty of examples and exercises.

Thanks