Forum Discussion
Solved
Excel IF Statement
See table below. I have multiple codes that have a prefix of a two-digit number followed by a three-digit number. I am trying to complete and IF statement that will for example, take all the values in column D IF they have a certain prefix in column B, for example 39.
The statement needs to handle a range. I tried =IF(B7=39-000:39-999,D7,0) hoping that all values in Column D would be selected if Column B value was in this range. If not in the range, assign
Column A Column B Column D
Cost Code Name Cost Code Total Hours
| 10" STORM | 39-110 | 14h 46m |
| 12" DRAIN BASIN | 39-312 | 112h 29m |
| 12" Reuse | 43-212 | 5h 15m |
| 12" Stab. Asphalt Subgrade | 31-100 | 314h 43m |
| 12" STORM | 39-112 | 958h 22m |
| 12" Water | 43-112 | 390h 23m |
| 15" HDPE | 39-115 | 540h 19m |
| 15" RCP Storm | 39-915 | 70h 05m |
| 18" DRAIN BASIN 7' | 39-618 | 16h 42m |
| 18" ENDWALL STRUCT (0-4 | 39-450 | 98h 31m |
| 18" MES | 39-318 | 29h 25m |
| 18" Precast MES | 39-418 | 6h 16m |
| 18" RCP Storm | 39-918 | 15h 52m |
| 18" STORM | 39-118 | 1076h 15m |
| 2" Gate Valve Water | 43-402 | 4h 59m |
| 2" HDPE POLY - IRRIGATION | 43-102 | 14h 37m |
| 2" Reuse Water | 43-202 | 64h 46m |
| 24" HDPE | 39-124 | 1140h 50m |
| 24" MES | 39-324 | 210h 38m |
| 24" RCP Storm | 39-924 | 235h 04m |
| 3" BFP & Hot Box | 43-404 | 8h 19m |
=IFERROR(INDEX($D$7:$D$178,SMALL(IF(NUMBERVALUE(LEFT($B$7:$B$178,2))=F$5,ROW($B$7:$B$178)-6),ROW(A1))),"")You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula is in cell F7 and filled across range F7:H47. The only change to the other formula is that this one refers dynamically to cells F5, G5 and H5 which hold the values 39, 41 and 43. I've removed the merged columns C and D in the attached file otherwise the formula doesn't work with reference to column D.
=IFERROR(INDEX($E$7:$E$165,SMALL(IF(NUMBERVALUE(LEFT($B$7:$B$178,2))=$G$5,ROW($B$7:$B$178)-6),ROW(A1))),"")You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The search criteria 39 is entered in cell G5 and can be changed dynamically. I've entered the Total in column E in order to avoid problems with the merged columns C and D.
- I probably did not explain it very well. On the spreadsheet I sent, I am looking to add three columns - say F, G, and H. I want all the 39 prefix hours (column D) to print in Column F, 41 prefix in Columnd D to come out on Column G and 43 prefix hours from Column D in Column H. The fact that each prefix has different three-digit numbers after the dash is what screwed me up. I could do it manually as it is only 173 rows of data but thought there could be an easier way. Appreciate your help and apologize for the confusion. Bob
=IFERROR(INDEX($D$7:$D$178,SMALL(IF(NUMBERVALUE(LEFT($B$7:$B$178,2))=F$5,ROW($B$7:$B$178)-6),ROW(A1))),"")You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula is in cell F7 and filled across range F7:H47. The only change to the other formula is that this one refers dynamically to cells F5, G5 and H5 which hold the values 39, 41 and 43. I've removed the merged columns C and D in the attached file otherwise the formula doesn't work with reference to column D.
