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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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
570
smozgur
smozgur
B
  • Question Question
average based on time
Replies
1
Views
387
baqer
B
K
  • Solved
Return Column header for MAX/MIN Value
Replies
3
Views
266
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
571
Greenbehindthecells
G
earthworm
  • Question Question
Split Data into separate worksheet based on Cell values
Replies
8
Views
593
earthworm
earthworm

Forum statistics

Threads
1,223,903
Messages
6,175,279
Members
452,630
Latest member
OdubiYouth

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