Find column when a sum on row cels with certain headers is >= of a value

P

pyrgas

New Member
Joined
Dec 5, 2018
Messages
8
[TABLE="width: 768"]
<tbody>[TR]
[TD="class: xl67, width: 64"][TABLE="width: 390"]
<tbody>[TR]
[TD="class: xl69, width: 29"]INV [/TD]
[TD="class: xl69, width: 37"]REIN[/TD]
[TD="class: xl69, width: 35"]BON[/TD]
[TD="class: xl69, width: 29"]WD[/TD]
[TD="class: xl69, width: 29"]INV[/TD]
[TD="class: xl69, width: 37"]REIN[/TD]
[TD="class: xl69, width: 35"]BON [/TD]
[TD="class: xl69, width: 29"]WD[/TD]
[TD="class: xl69, width: 29"]INV[/TD]
[TD="class: xl69, width: 37"]REIN[/TD]
[TD="class: xl69, width: 35"]BON[/TD]
[TD="class: xl69, width: 29"] WD[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 390"]
<tbody>[TR]
[TD="width: 29, align: right"]10[/TD]
[TD="width: 37, align: right"]20[/TD]
[TD="width: 35, align: right"]30[/TD]
[TD="width: 29, align: right"]40[/TD]
[TD="width: 29, align: right"]100[/TD]
[TD="width: 37, align: right"]50[/TD]
[TD="width: 35, align: right"]20[/TD]
[TD="width: 29, align: right"]30[/TD]
[TD="width: 29, align: right"]40[/TD]
[TD="width: 37, align: right"]50[/TD]
[TD="width: 35, align: right"]20[/TD]
[TD="width: 29, align: right"]40[/TD]
[/TR]
</tbody>[/TABLE]

Hi everybody, i am searching for a formula that gives me the column number when the sum on row(b) with header "REIN" is >= 70
Thank's in advance
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
Hi & welcome to MrExcel
How about

Excel 2013/2016
ABCDEFGHIJKL
1INVREINBONWDINVREINBONWDINVREINBONWD
21020304010050203040702040
3
410
Color
Cell Formulas
RangeFormula
A4{=IFERROR(MATCH(1,(A1:L1="REIN")*(A2:L2>=70),0),"Not found")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Perhaps this?

ABCDEFGHIJKLM
INVREINBONWDINVREINBONWDINVREINBONWD

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A4[/TH]
[TD="align: left"]{=MIN(IF(MMULT(COLUMN(A1:L1)^0,TRANSPOSE(IF(COLUMN(A1:L1)<=TRANSPOSE(COLUMN(A1:L1)),IF(A1:L1="REIN",A2:L2,0),0)))>=70,COLUMN(A1:L1)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If
entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thank's for the quick reply, this is what i was searching for. I am not so experience in ecxel so i will try how this is working.
 
Upvote 0
Fluff said:
Hi & welcome to MrExcel
How about
Excel 2013/2016
ABCDEFGHIJKL
INVREINBONWDINVREINBONWDINVREINBONWD

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]40[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Color​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A4[/TH]
[TD="align: left"]{=IFERROR(MATCH(1,(A1:L1="REIN")*(A2:L2>=70),0),"Not found")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
Thank's for the answer.
This is more simple that second answer but when i tried this in excel 2013 but the result was "Not found"
 
Upvote 0
Eric had a different interpretation of what you were after, than myself. So I suspect that I misunderstood what you wanted.
 
Upvote 0
Fluff's formula would find the first column that has REIN in row 1, and a value >= 70 in row 2.

My formula summed up the values of the REIN cells from left to right. So it returned a value of 6, since in this example B2+F2=70, so I returned 6 for column F.

Let us know what you're actually looking for, and we can figure out which formula to use and/or adapt to your needs.
 
Upvote 0
Eric W said:
Perhaps this?

ABCDEFGHIJKLM
INVREINBONWDINVREINBONWDINVREINBONWD

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]A4[/TH]
[TD="align: left"]{=MIN(IF(MMULT(COLUMN(A1:L1)^0,TRANSPOSE(IF(COLUMN(A1:L1)<=TRANSPOSE(COLUMN(A1:L1)),IF(A1:L1="REIN",A2:L2,0),0)))>=70,COLUMN(A1:L1)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If
entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


Eric hi,
Please can you give me the formula for the same problem when we transpose the file so as to find the row for the same condition? I made a fund with a lot of investors and i need it to see when they reach a certain amount of investing money to change the percentage. We can talk in private also if you are interested in such an investion.
Regards
Ioannis
 
Upvote 0
Do you mean something like this?

Excel 2012
ABCDE
MonthAmountTriggerMonth
JanJun
FebJun
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]250[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]{=IFERROR(INDEX(A2:A13,SMALL(IF(TRANSPOSE(MMULT(TRANSPOSE(B2:B13),IF(ROW(A2:A13)<=TRANSPOSE(ROW(A2:A13)),1,0)))>=D2,ROW(A2:A13)-ROW(A2)+1),1)),"Trigger not met")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]{=IFERROR(INDEX(A2:A13,SMALL(IF(SUBTOTAL(9,OFFSET(B2,0,0,ROW(A2:A13)-ROW(A2)+1))>=D2,ROW(A2:A13)-ROW(A2)+1),1)),"Trigger not met")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



The E3 formula is somewhat shorter, but if you have additional conditions, we'll probably have to modify the E2 formula.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
If Then else in Excel
Replies
9
Views
361
Gulfcourse
G
H
  • Solved
Split file into multiple files based on year in column
Replies
6
Views
277
Hasson
H
J
  • Question Question
Trying to add numbers when grouped together.
Replies
2
Views
84
jwillits
J
H
  • Question Question
Print out range for specific sheet based on two comboboxes on userform
Replies
1
Views
220
Hasson
H
T
  • Question Question
Running Average of last n row if sum is between a certain value
Replies
1
Views
72
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

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