 New Contributor

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

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.

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

5 Replies

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

``=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. # Re: divide a cell value amongst one column that begins with and another column that begins with.

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)

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

``=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. # Re: divide a cell value amongst one column that begins with and another column that begins with.

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

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

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

You can try SUMPRODUCT. 