cyrilbrd
Well-known Member
- Joined
- Feb 2, 2012
- Messages
- 4,113
- Office Version
- 365
- Platform
- Windows
- Mobile
Given in A1:
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: center; }</style>
<colgroup><col style="width:65pt" span="5" width="65"> </colgroup><tbody>
</tbody>
formula in A2 is =IF(AND(B2>C2,D2>E2),"yes","") hence conditions for the row are R1 superior to R2 AND R3 superior to R4.
Result Expected in G1:
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: center; }</style>
<colgroup><col style="width:65pt" span="6" width="65"> </colgroup><tbody>
</tbody>formula presently used in H2 is =IF(ROWS($H$2:H2)>SUMPRODUCT(--(B:B>C:C),--(D:D>E:E)),"","yes") CSE copied down till needed
formula presently used in I2 is =IF(ROWS(I$2:I2)>SUMPRODUCT(--($B:$B>$C:$C),--($D:$D>$E:$E)),"",INDEX(B:B,SMALL(IF($A$2:$A$7=$H2,ROW($A$2:$A$7)),ROWS(I$2:I2)))) CSE copied right till Column L and down till needed.
NOTE:
The above scenario in A1 involves the use of a helper column in A returning either a 'yes' or a blank
The above results in G1 make use of Column A in order to retrieve all INDEX whenever the condition is met
QUESTION:
Could it be possible to invoke the two conditions within the search formula thus removing the need of Column A while returning all values fitting the conditions:
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: center; }</style>
<colgroup><col style="width:65pt" span="6" width="65"> </colgroup><tbody>
</tbody>
Count would be =SUMPRODUCT(--(B:B>C:C),--(D:D>E:E)) CSE
but what would be the formula in I2?
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: center; }</style>
identifier | R1 | R2 | R3 | R4 |
yes | 16 | 15 | 54 | 53 |
23 | 15 | 12 | 55 | |
yes | 34 | 14 | 54 | 50 |
55 | 56 | 100 | 89 | |
yes | 80 | 31 | 32 | 24 |
45 | 78 | 83 | 93 |
<colgroup><col style="width:65pt" span="5" width="65"> </colgroup><tbody>
</tbody>
formula in A2 is =IF(AND(B2>C2,D2>E2),"yes","") hence conditions for the row are R1 superior to R2 AND R3 superior to R4.
Result Expected in G1:
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: center; }</style>
R1_r | R2_r | R3_r | R4_r | ||
result | yes | 16 | 15 | 54 | 53 |
yes | 34 | 14 | 54 | 50 | |
yes | 80 | 31 | 32 | 24 | |
<colgroup><col style="width:65pt" span="6" width="65"> </colgroup><tbody>
</tbody>
formula presently used in I2 is =IF(ROWS(I$2:I2)>SUMPRODUCT(--($B:$B>$C:$C),--($D:$D>$E:$E)),"",INDEX(B:B,SMALL(IF($A$2:$A$7=$H2,ROW($A$2:$A$7)),ROWS(I$2:I2)))) CSE copied right till Column L and down till needed.
NOTE:
The above scenario in A1 involves the use of a helper column in A returning either a 'yes' or a blank
The above results in G1 make use of Column A in order to retrieve all INDEX whenever the condition is met
QUESTION:
Could it be possible to invoke the two conditions within the search formula thus removing the need of Column A while returning all values fitting the conditions:
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: center; }</style>
count | 3 | R1 | R2 | R3 | R4 |
Result | 16 | 15 | 54 | 53 | |
34 | 14 | 54 | 50 | ||
80 | 31 | 32 | 24 |
<colgroup><col style="width:65pt" span="6" width="65"> </colgroup><tbody>
</tbody>
Count would be =SUMPRODUCT(--(B:B>C:C),--(D:D>E:E)) CSE
but what would be the formula in I2?