I have an excel table with 2 columns of data. A GL account and a cost. I’m trying to return a single calculated cell with the criteria being a selection of GL rows based on a middle 4 characters within the GL code itself. The result is then to SUM the cost for these selected rows. In this example the the criteria is MID([GL Code],2,4) where it’s either ‘1234’ or ‘1134’.
My table looks like
| GL Code | Cost |
|---|---|
| 1123400 | 10 |
| 1123400 | 25 |
| 1123400 | 100 |
| 1234567 | 1000 |
| 1113400 | 50 |
| 1234567 | 200 |
| 1113400 | 50 |
I’m needing a formula to return a single calculated cell of $235.
I can achieve this if I create another column in this sheet to already execute the MID function, and then use SUMIF to calculate, however I’m wanting to achieve this all within one code without creating another column. This could WITH creating another column is =SUMIF(A2:A8, "1234",B2:B8) + SUMIF(A2:A8, "1134",B2:B8), which I’m wanting to incorporate MID (or an alternative) into
TIA
>Solution :
Try the following formula-
=SUM(LET(m,--MID(A2:A8,2,4),FILTER(B2:B8,(m=1234)+(m=1134))))
