dickpierce
Board Regular
- Joined
- Nov 26, 2004
- Messages
- 148
- Office Version
- 2007
- Platform
- Windows
Hello!
If I use the formula in H2 and bring it down to H7, I get the desired results, unfortunately when it reaches H8 I get the #NUM result. I know that if I change the ROWS(F$2:F8) to ROWS(F$8:F8) I get the required result, but I'm just wondering if there's a way that would do it automatically. Thanks in advance for your help.
F G H
1 Alabama 6 ={INDEX($C$2:$C$1800,SMALL(IF($A$2:$A$1800=F2,ROW(A$2:$A$1800)-1),ROWS(F$2:F2)))}
1 Alabama 2
1 Alabama 15
1 Alabama 8
1 Alabama 13
1 Alabama 3
2 Ohio St #NUM !
2 Ohio St #NUM !
2 Ohio St #NUM !
2 Ohio St #NUM !
2 Ohio St #NUM !
2 Ohio St #NUM !
3 Georgia #NUM !
3 Georgia #NUM !
3 Georgia #NUM !
3 Georgia #NUM !
3 Georgia #NUM !
3 Georgia #NUM !
If I use the formula in H2 and bring it down to H7, I get the desired results, unfortunately when it reaches H8 I get the #NUM result. I know that if I change the ROWS(F$2:F8) to ROWS(F$8:F8) I get the required result, but I'm just wondering if there's a way that would do it automatically. Thanks in advance for your help.
F G H
1 Alabama 6 ={INDEX($C$2:$C$1800,SMALL(IF($A$2:$A$1800=F2,ROW(A$2:$A$1800)-1),ROWS(F$2:F2)))}
1 Alabama 2
1 Alabama 15
1 Alabama 8
1 Alabama 13
1 Alabama 3
2 Ohio St #NUM !
2 Ohio St #NUM !
2 Ohio St #NUM !
2 Ohio St #NUM !
2 Ohio St #NUM !
2 Ohio St #NUM !
3 Georgia #NUM !
3 Georgia #NUM !
3 Georgia #NUM !
3 Georgia #NUM !
3 Georgia #NUM !
3 Georgia #NUM !