divide a cell value amongst one column that begins with and another column that begins with.

[DSHHI]

So this is a little bit of a complicated one....

Scenario:

I have a budget sheet that is broken out by the first 2 numbers of a spec section.  I have an import sheet for another program that i would like to load my budget number to.  I would like to take the budget amount and divide it evenly (rounding up), amongst all the line items that have the same first 2 digits.

Additionally, I would like to only divide it among the installation activities for that specific spec section.

So, basically.....

If numbers in column I, on sheet 2, begin with the same left 2 digits in column A on sheet 1

And

items in Column D of sheet 2 begin with "INST" 

Then

Divide the corresponding column K number on sheet 1 evenly among all "INST" activities with same first 2 digits...

I'm pretty sure its a big mix of IF, AND, MATCH, LEFT, VLOOKUP, DIVIDE, ROUND functions.

Not sure if anyone can help me crack this one or not, but would be a great help.

dropbox link to workbook:

https://www.dropbox.com/scl/fi/wr5r3naeiuo61p2o0p4r9/formula-question.xlsx?dl=0&rlkey=qmsqwdv07761ly...

I have the initial part working... I just need to get the division part 

=IF(AND((LEFT(I3,2)=VLOOKUP(LEFT(I3,2),'Section Costs'!$A$3:$K$38,1,FALSE)),(LEFT(D3,4)="INST")),VLOOKUP(LEFT(I3,2),'Section Costs'!$A$3:$K$38,11,FALSE),"NO BDGT")

[#2]

@DSHHI 

=IF(MATCH(A1,$E$1:$E$3,0),VLOOKUP(A1,$E$1:$F$3,2,FALSE)/COUNTIF($A$1:$A$20,A1),"")

Is this what you are looking for? Unfortunately I can't work with your file but in my understanding this should return the division part.

 

[#3]

thats almost exactly what i have except my digits are longer than 2 so i'm trying to pull only the left 2 digits and its not liking that.... This is what i've gotten to, but it fails right at the end in the criteria part.....

=IF(AND((LEFT(I17,2)=VLOOKUP(LEFT(I17,2),'Section Costs'!$A$3:$K$38,1,FALSE)),(LEFT(D17,4)="INST")),VLOOKUP(LEFT($I17,2),'Section Costs'!$A$3:$K$38,11,FALSE)/(COUNTIF(LEFT($I$3:$I$493,2),(LEFT($I17,2)))),FALSE)

[#4]

@DSHHI 

=IFERROR(IF(MATCH(LEFT(A1,2),LEFT($E$1:$E$3,2),0),VLOOKUP(LEFT(A1,2),CHOOSE({1,2},LEFT($E$1:$E$3,2),$F$1:$F$3),2,0)/COUNTIF($A$1:$A$20,A1),""),"")

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021. This suggestion is with VLOOKUP and CHOOSE however it should be easier with INDEX and MATCH.

[#5]

my formula is working all the way up until I try and divide. I get the proper value returned, but its just when i try and divide at the end is when it goes wrong. its the countif portions that are not returning the values...

[#6]

@DSHHI 

=SUMPRODUCT(N(LEFT(I3,2)=LEFT($I$3:$I$24,2)))

You can try SUMPRODUCT.