Formula to return nth cell in a sequence with criteria

G

graemestown

New Member
Joined
May 10, 2019
Messages
20
Office Version
  1. 365
Platform
  1. Windows
https://www.dropbox.com/s/amwx4p2ofoau8w3/Example.xlsx?dl=0

[TABLE="width: 1408"]
<colgroup><col width="64" style="width:48pt"> <col width="64" span="20" style="width:48pt"> <col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl71, width: 64"]1[/TD]
[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"]B[/TD]
[TD="class: xl65, width: 64"]C[/TD]
[TD="class: xl65, width: 64"]D[/TD]
[TD="class: xl65, width: 64"]E[/TD]
[TD="class: xl65, width: 64"]F[/TD]
[TD="class: xl65, width: 64"]G[/TD]
[TD="class: xl65, width: 64"]H[/TD]
[TD="class: xl65, width: 64"]I[/TD]
[TD="class: xl65, width: 64"]J[/TD]
[TD="class: xl65, width: 64"]K[/TD]
[TD="class: xl65, width: 64"]L[/TD]
[TD="class: xl65, width: 64"]M[/TD]
[TD="class: xl65, width: 64"]N[/TD]
[TD="class: xl65, width: 64"]O[/TD]
[TD="class: xl65, width: 64"]P[/TD]
[TD="class: xl65, width: 64"]Q[/TD]
[TD="class: xl65, width: 64"]R[/TD]
[TD="class: xl65, width: 64"]S[/TD]
[TD="class: xl65, width: 64"]T[/TD]
[TD="class: xl65, width: 64"]U[/TD]
[/TR]
[TR]
[TD="class: xl71"]2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"]3[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]6[/TD]
[TD="class: xl66"]7[/TD]
[TD="class: xl66"]8[/TD]
[TD="class: xl66"]9[/TD]
[TD="class: xl66"]10[/TD]
[TD="class: xl66"]11[/TD]
[TD="class: xl66"]12[/TD]
[TD="class: xl66"]13[/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"]15[/TD]
[TD="class: xl66"]16[/TD]
[TD="class: xl66"]17[/TD]
[TD="class: xl66"]18[/TD]
[TD="class: xl66"]19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"]4[/TD]
[TD="class: xl66"]Bread[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl65"]969[/TD]
[TD="class: xl65"]883[/TD]
[TD="class: xl65"]638[/TD]
[TD="class: xl65"]494[/TD]
[TD="class: xl65"]112[/TD]
[TD="class: xl65"]750[/TD]
[TD="class: xl65"]683[/TD]
[TD="class: xl72"]986[/TD]
[TD="class: xl65"]273[/TD]
[TD="class: xl65"]390[/TD]
[TD="class: xl65"]993[/TD]
[TD="class: xl65"]566[/TD]
[TD="class: xl65"]465[/TD]
[TD="class: xl65"]127[/TD]
[TD]=Formula[/TD]
[/TR]
[TR]
[TD="class: xl71"]5[/TD]
[TD="class: xl66"]Cake[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl65"]320[/TD]
[TD="class: xl65"]508[/TD]
[TD="class: xl65"]306[/TD]
[TD="class: xl65"]833[/TD]
[TD="class: xl65"]100[/TD]
[TD="class: xl65"]361[/TD]
[TD="class: xl65"]53[/TD]
[TD="class: xl72"]364[/TD]
[TD="class: xl65"]323[/TD]
[TD="class: xl65"]844[/TD]
[TD="class: xl65"]873[/TD]
[TD="class: xl65"]113[/TD]
[TD="class: xl65"]575[/TD]
[TD="class: xl65"]963[/TD]
[TD="class: xl65"]415[/TD]
[TD="class: xl65"]66[/TD]
[TD="class: xl65"]557[/TD]
[TD]=Formula[/TD]
[/TR]
[TR]
[TD="class: xl71"]6[/TD]
[TD="class: xl66"]Pies[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl65"]320[/TD]
[TD="class: xl65"]720[/TD]
[TD="class: xl65"]480[/TD]
[TD="class: xl65"]574[/TD]
[TD="class: xl65"]203[/TD]
[TD="class: xl65"]607[/TD]
[TD="class: xl65"]54[/TD]
[TD="class: xl72"]900[/TD]
[TD="class: xl65"]796[/TD]
[TD="class: xl65"]807[/TD]
[TD="class: xl65"]774[/TD]
[TD]=Formula[/TD]
[/TR]
[TR]
[TD="class: xl71"]7[/TD]
[TD="class: xl66"]Rolls[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl65"]602[/TD]
[TD="class: xl65"]404[/TD]
[TD="class: xl65"]801[/TD]
[TD="class: xl65"]340[/TD]
[TD="class: xl65"]977[/TD]
[TD="class: xl65"]767[/TD]
[TD="class: xl65"]583[/TD]
[TD="class: xl72"]933[/TD]
[TD="class: xl65"]731[/TD]
[TD="class: xl65"]285[/TD]
[TD="class: xl65"]335[/TD]
[TD="class: xl65"]833[/TD]
[TD="class: xl65"]462[/TD]
[TD="class: xl65"]858[/TD]
[TD="class: xl65"]872[/TD]
[TD="class: xl65"]360[/TD]
[TD="class: xl65"]932[/TD]
[TD]=Formula[/TD]
[/TR]
[TR]
[TD="class: xl71"]8[/TD]
[TD="class: xl66"]Donuts[/TD]
[TD="class: xl73"]12[/TD]
[TD="class: xl73"]16[/TD]
[TD="class: xl73"]20[/TD]
[TD="class: xl73"]23[/TD]
[TD="class: xl70"]92[/TD]
[TD="class: xl70"]121[/TD]
[TD="class: xl70"]717[/TD]
[TD="class: xl70"]523[/TD]
[TD="class: xl70"]105[/TD]
[TD="class: xl70"]180[/TD]
[TD="class: xl70"]263[/TD]
[TD="class: xl72"]98[/TD]
[TD="class: xl65"]923[/TD]
[TD="class: xl65"]138[/TD]
[TD="class: xl65"]470[/TD]
[TD="class: xl65"]516[/TD]
[TD="class: xl65"]460[/TD]
[TD="class: xl65"]518[/TD]
[TD="class: xl65"]266[/TD]
[TD]=Formula[/TD]
[/TR]
[TR]
[TD="class: xl71"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl69, colspan: 2"]Criteria for formula:[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"][/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl68, colspan: 12"]Ignores blank cells and allows for an 'if greater than' statement. For example it ignores all the bold cells because they are less than 50[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"][/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl68, colspan: 5"]Then counts 8 cells from the start of the valid sequence[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"][/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl68, colspan: 6"]Formula returns the value in the 8th cell, the result I want returned is italic & underlined[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"][/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl68, colspan: 11"]Formula would be in column u which I could drag down and would work regardless of where the sequence starts[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1280"]
<colgroup><col width="64" span="20" style="width:48pt"></colgroup><tbody></tbody>[/TABLE]
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
Array formula

{=OFFSET(A3,0,MIN(IF(B3:T3<>"",IF(B3:T3>50,COLUMN(B3:T3)-2)))+8,,)}

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
 
Upvote 0
Hi, could you help me again.

How would I sum the first 8 cells after criteria have been met - the criteria would be to ignore the first blank cells or cells less than 50.
So in the donuts row it would ignore values 12,16,20,23 and then sum the next 8 cells thereafter, 92+121+717+523+105+180+263+98=2,099

Thanks!
 
Upvote 0
graemestown said:
Hi, could you help me again.

How would I sum the first 8 cells after criteria have been met - the criteria would be to ignore the first blank cells or cells less than 50.
So in the donuts row it would ignore values 12,16,20,23 and then sum the next 8 cells thereafter, 92+121+717+523+105+180+263+98=2,099

Thanks!
Click to expand...


It is an array formula

{=SUM(OFFSET(A3,0,MIN(IF(B3:T3>50,COLUMN(B3:T3)-1)),,8))}
 
Upvote 0
Hi Again

The spreadsheet you are helping me build is incredible.

Could you help me write a formula that would do this:
For example in the Bread row, I need a formula that would return the week that the sales first started. Sales were first recorded in week 6 with 969 units.
So the criteria would be to ignore the first blank cells or cells less than 50, find the first cell, in this case 969 units, then the answer would be (week) 6.
In the cake row the answer would be (week) 3.
In the Donuts row the answer would be (week) 5.
 
Upvote 0
graemestown said:
Hi Again

The spreadsheet you are helping me build is incredible.

Could you help me write a formula that would do this:
For example in the Bread row, I need a formula that would return the week that the sales first started. Sales were first recorded in week 6 with 969 units.
So the criteria would be to ignore the first blank cells or cells less than 50, find the first cell, in this case 969 units, then the answer would be (week) 6.
In the cake row the answer would be (week) 3.
In the Donuts row the answer would be (week) 5.
Click to expand...

It is an array formula



{=INDEX($B$2:$T$2,,MIN(IF(B3:T3>50,COLUMN(B3:T3)-1)))}
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
646
smozgur
smozgur
B
  • Question Question
average based on time
Replies
1
Views
395
baqer
B
K
  • Solved
Return Column header for MAX/MIN Value
Replies
3
Views
268
kitm
K
G
  • Question Question
Formula to count with multiple criteria and exclude from a separate column, then sort by count in one cell with spill
Replies
0
Views
611
Greenbehindthecells
G
earthworm
  • Question Question
Split Data into separate worksheet based on Cell values
Replies
8
Views
595
earthworm
earthworm

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top