• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Summing a dynamic range

odartey

odartey

Member
Hi all,

I will want help with summing a dynamic range after i have used advanced filtering to query from a database to another worksheet in the same workbook.

This is what i mean, when we query a database, depending on what you are looking for, the data could be more or less.Now weather it is more or less, i what a macro or a vba to sum the relevant ranges when ever the data is spooled out, example as found below;

eg 1,

Jan Feb Mar

45 67 67

23 45 23

45 90 56


eg 2

Jan Feb Mar

67 90 34

78 45 100

23 56 78

34 87 90

23 67 89


I will be grateful if anyone out there could be of help.

Thanks
 
Odarty


It is unclear what you want to sum


If you want to Sum January you can use something like:

Code: 
=Sum(Offset(a1,1,0,counta(a:a)-1))


If this doesn't help can you be more specific


You may also want to read: http://chandoo.org/wp/2012/09/17/offset-formula-explained/


Depending on the exact application you can probably do that without the Advanced Filter aswell
 
Thanks Hui,

What i really need is to be able to sum the relevant ranges whenever the ranges change.

eg, if the range i summed now was D12:Q15,after querying my database with advanced filtering, the next time i query my database, it gives me D12:Q25, how then do i sum the change in ranges.


Thanks
 
=Sum(Offset(D12,0,0,counta(q12:q100000),14))


You may also want to read: http://chandoo.org/wp/2012/09/17/offset-formula-explained/
 
Thanks.


it did work, but summed only the last column ie Q. I will want it to sum from D12:Q12 downwards whenever the range changes, can the formula be incorporated in a macro or vb so that i will not have to be changing the position of my result cell.
 
Hi,


In that case you need to add another counta(), with your data in A2:C8 and this formula is in C1:


Code: 
=SUM(OFFSET(A2,,,COUNTA(A:A),COUNTA(2:2)))


Regards.
 
Odarty


My last formula =Sum(Offset(D12,0,0,counta(q12:q100000),14))

does exactly what you have asked

It sums a range from D12:Qx based on the height of data in Column Q from Q12: down

Do you also want to change the width (14 above) as well?


Can you post a sample file, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
You must log in or register to reply here.
Back
Top