How to count

New Contributor

Hi, need some help with below topic :

- in the template, the range B1:B12 - have some numbers

- want to summary on those columns , which are define by number , which is indeecated separatly


eg. B1- 2;B2-1;B3-5 ; in the other cell (eg.J1)have a formula's result eg.2 , so want to count only 2 cells - b1&b2; result in J1 can change to eg3 - so I want to calculate than all3 numbers from range B1:B3.


How to do that? Need help

8 Replies



You can try this formula if you want to sum the ranges.

sum indirect.JPG 


A non-volatile formula that requires Excel 365

= SUM(TAKE(data, N))

where 'data' is $B$1:$B$15 and 'N' is $J$1.



Have tried witha rows and failed




What should I change?

best response confirmed by Tom67K (New Contributor)



This formula returns the intended result in my sheet.

sum dynamic ranges.JPG


For me it changes the formula to

= SUM(TAKE(data,1,N))


I prefer that to the INDIRECT!

@Peter Bartholomew 

Thanks , for your support.

You are amazing, Thx !