LOOK FOR VALUE, THEN TAKE NEXT 12 COLUMNS AND SUM THEM

need a formula to find the column where an array crosses a certain threshold

so need to find the column number where that happens

threshold is 19,000 , looking for the first time an array crosses that mark

note- an array is between columns H-CA

then i need to sum the NEXt twelve columns of data.

so for example, if COL M has 17,500 AND col N has 19,400, and this was first time we crossed 19,000 (ie it didnt happen in colmns A-L, then i  need to sum columns O-Z

need to do this across rows 7-480
finnstoneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

abbas abdullaCommented:
Hi,

Check entering below function using keyboard combinations Ctrl+Shift+Enter

=AVERAGE(INDIRECT(ADDRESS(7,SMALL(IF(H7:CA700>=19000,COLUMN(H7:CA700),),1))& ":"&ADDRESS(700,SMALL(IF(H7:CA700>=19000,COLUMN(H7:CA700),),1)+11),1))
finnstoneAuthor Commented:
sorry did not work

#value
abbas abdullaCommented:
Unfortunately I have no access now to excel but same function is working in google sheets. Check this link https://docs.google.com/spreadsheets/d/11OqfWNeAo99TPlqHO3kzK2CSqkl3Ee98JNscRh83ROY

I will test function using excel tomorrow if you didn't get solution. Can you please post dummy data sample that shows what you are trying to achieve?
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
So if a value greater than 19000 (say 19400) is found in column N, do you want to sum the values in the range O7:Z480?
If so, try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

=SUM(OFFSET(INDIRECT(ADDRESS(7,COLUMN(INDEX(H7:CA480,SMALL(IF(H7:CA480>19000,ROW(H7:CA480)-ROW(H7)+1),1),SMALL(IF(H7:CA480>19000,COLUMN(H7:CA7)-COLUMN(H7)+1),1))))),,1,474,12))

Open in new window

finnstoneAuthor Commented:
can you change this ti just sum the row that it is working on please?

also can you create one that does the prior 12 columns before 19000. thanks!!!!
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I have logged off as it is 2 AM here and on mobile right now. I will check it tomorrow.
Btw do you mean if 19400 is found in N15, you want to sum only O15:Z15?
finnstoneAuthor Commented:
YES O:Z
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please give this a try...

=SUM(OFFSET(INDEX(H7:CA480,SMALL(IF(H7:CA480>19000,ROW(H7:CA480)-ROW(H7)+1),1),SMALL(IF(H7:CA480>19000,COLUMN(H7:CA7)-COLUMN(H7)+1),1)),,1,1,12))

Open in new window


For prior 12...
=SUM(OFFSET(INDEX(H7:CA480,SMALL(IF(H7:CA480>19000,ROW(H7:CA480)-ROW(H7)+1),1),SMALL(IF(H7:CA480>19000,COLUMN(H7:CA7)-COLUMN(H7)+1),1)),,-12,1,12))

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
finnstoneAuthor Commented:
THANKS. I am starting a new question for you
finnstoneAuthor Commented:
finnstoneAuthor Commented:
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You awarded only 500 bonus points. Why?
finnstoneAuthor Commented:
hm I will fix that. sorry i have not used the new scoring system too much yet, so thought i did max points
finnstoneAuthor Commented:
i will open a ticket
finnstoneAuthor Commented:
can you do this one for me though?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.