# 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
Microsoft ExcelMicrosoft Office

Last Comment
finnstone
abbas abdulla

Hi,

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

finnstone

sorry did not work

#value
abbas abdulla

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?
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))
``````
finnstone

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!!!!
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?
finnstone

YES O:Z
Subodh Tiwari (Neeraj)

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
finnstone

THANKS. I am starting a new question for you
You awarded only 500 bonus points. Why?
finnstone

hm I will fix that. sorry i have not used the new scoring system too much yet, so thought i did max points
finnstone

i will open a ticket
finnstone

can you do this one for me though?
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY