Hi,
I want to sum values in a column from say row 1 to row 100. Now the items from 1 to row 100 vary ...............so i want to sum all the contents something like this
=sum(E1:E & End_row)
i get an error.........see enclosed file.
Hi,
I want to sum values in a column from say row 1 to row 100. Now the items from 1 to row 100 vary ...............so i want to sum all the contents something like this
=sum(E1:E & End_row)
i get an error.........see enclosed file.
You cannot use E& End_row in excel like in VBA. You actually dont need the count function.
Just use =sum(E:E)
your right i could do that but in my application i can not...........i have to sum from a begining row to an end row.
Try:
=SUM(E1:INDEX(E:E,MATCH(9.99999E+307,E:E)))
or
=SUM(E1:INDEX(E:E,end_row))
but that assumes no blanks and that the numbers start at E1 always.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
You could use this
=SUM(A1:A100,(IF(COUNT(A101:A45000)>0,OFFSET(A101,0,0,COUNT(A101:A45000)),0)))
I have just assumed that your column will go on till row 45000
NBVC,
What is 9.99999E+307? Also, where have you defined end_row?
That is the largest number Excel recognizes and so trying to MATCH that number without using the 0 match_type argument for exact match will yield the last number in the column that is smaller than or equal to that large number... that would inevitably be the last number in the column
end_row was pre-defined by the OP as cell M16
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks