Dear All,
I have very hard question.
Criteria's
- Cell G2 I'm looking for Sales Agent named 'A'
- Cell K2 looking contract status of 'Settlement'.
- Cell L2 $500,000
I'm trying find out row number when the Sales Agent named, 'A', first
surpasses cumulative settlement sales of $500,000. I have manually highlighted
in Column B what amounts cumulatively add up to $500,000. My formula in Cell H2
is incorrect as should be return row number 14 as per cell I2.
Please note I’m looking for solutions without VBA or formula arrays.
Your help regarding this matter would be greatly appreciated.
<title>Excel Jeanie HTML</title><title>Excel Jeanie HTML</title><!-- ######### Start Created Html Code To Copy ########## -->
Sheet1
A | B | C | D | E | F | G | H | I | J | K | L | |
Sales Agent | Value | Contract Status | Sales Agent | Row Number | Should Be | Contract Status | Sales Amount | |||||
A | Settlement | A | ||||||||||
B | Settlement | |||||||||||
C | Settlement | |||||||||||
D | Settlement | |||||||||||
A | Qualified | |||||||||||
B | Settlement | |||||||||||
C | Settlement | |||||||||||
D | Settlement | |||||||||||
A | Settlement | |||||||||||
B | Settlement | |||||||||||
C | Settlement | |||||||||||
D | Settlement | |||||||||||
A | Settlement | |||||||||||
B | Settlement | |||||||||||
C | Settlement | |||||||||||
D | Settlement | |||||||||||
A | Settlement | |||||||||||
B | Settlement | |||||||||||
C | Settlement | |||||||||||
D | Settlement |
<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 79px;"><col style="width: 81px;"><col style="width: 101px;"><col style="width: 9px;"><col style="width: 9px;"><col style="width: 11px;"><col style="width: 79px;"><col style="width: 88px;"><col style="width: 69px;"><col style="width: 9px;"><col style="width: 101px;"><col style="width: 93px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="bgcolor: #CCFFCC, align: right"] 240,681.00 [/TD]
[TD="bgcolor: #CCFFCC, align: right"]44[/TD]
[TD="align: right"] 14.00 [/TD]
[TD="bgcolor: #FFFFCC"]Settlement[/TD]
[TD="bgcolor: #FFFFCC, align: right"]500,000[/TD]
[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: right"] 243,173.00 [/TD]
[TD="bgcolor: #FFFFCC"]Qualifed[/TD]
[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: right"] 201,350.00 [/TD]
[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: right"] 259,721.00 [/TD]
[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: right"] 116,111.00 [/TD]
[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="align: right"] 120,828.00 [/TD]
[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="align: right"] 207,966.00 [/TD]
[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="align: right"] 273,373.00 [/TD]
[TD="bgcolor: #CACACA, align: center"]10[/TD]
[TD="bgcolor: #CCFFCC, align: right"] 126,762.00 [/TD]
[TD="bgcolor: #CACACA, align: center"]11[/TD]
[TD="align: right"] 133,826.00 [/TD]
[TD="bgcolor: #CACACA, align: center"]12[/TD]
[TD="align: right"] 186,833.00 [/TD]
[TD="bgcolor: #CACACA, align: center"]13[/TD]
[TD="align: right"] 265,714.00 [/TD]
[TD="bgcolor: #CACACA, align: center"]14[/TD]
[TD="bgcolor: #CCFFCC, align: right"] 152,705.00 [/TD]
[TD="bgcolor: #CACACA, align: center"]15[/TD]
[TD="align: right"] 206,310.00 [/TD]
[TD="bgcolor: #CACACA, align: center"]16[/TD]
[TD="align: right"] 120,429.00 [/TD]
[TD="bgcolor: #CACACA, align: center"]17[/TD]
[TD="align: right"] 205,981.00 [/TD]
[TD="bgcolor: #CACACA, align: center"]18[/TD]
[TD="align: right"] 191,144.00 [/TD]
[TD="bgcolor: #CACACA, align: center"]19[/TD]
[TD="align: right"] 162,691.00 [/TD]
[TD="bgcolor: #CACACA, align: center"]20[/TD]
[TD="align: right"] 274,247.00 [/TD]
[TD="bgcolor: #CACACA, align: center"]21[/TD]
[TD="align: right"] 223,136.00 [/TD]
</tbody>
Spreadsheet Formulas | ||||
<tbody> </tbody> |
<tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
<!-- ######### End Created Html Code To Copy ########## -->
<!-- ######### Start Created Html Code To Copy ########## -->
Kind Regards,
Biz