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

Copper Contributor

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



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


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


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:



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")

5 Replies



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.

distributing value.JPG 

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)



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.

equal distribution.JPG

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...



You can try SUMPRODUCT.