If your range is say from A1:A100, then this formula will find the first 5 and sum until it finds the last 5...but the 5's have to all be grouped together or it will include the values between to:
=SUM(INDEX(A1:A100,MATCH(5,A1:A100,0)):INDEX(A1:A100,MATCH(2,1/(A1:A100=5))))
adjust ranges to suit and change the 5's to whatever number or reference you desire.
formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER
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.
thanks for that works a treat
which bit of it would i change if i wanted to sum column B that related to the fives in column A
so if i put a series of 5's in column A for a product it would sum the totals in column B
Just adjust the first ranges in the Index() functions.
e.g.
=SUM(INDEX(B1:B100,MATCH(5,A1:A100,0)):INDEX(B1:B100,MATCH(2,1/(A1:A100=5))))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks