SOLVED

Allocating cost formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2251390%22%20slang%3D%22en-US%22%3EAllocating%20cost%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2251390%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20day%3C%2FP%3E%3CP%3EThe%20table%20below%20shows%20a%20project%20%23%20and%20in%20certain%20cases%26nbsp%3B%20multiple%20locations%20per%20project.%20The%20cost%20for%20each%20project%20is%20charged%20to%20%22Central%22%20Location%20as%20shown%20in%20the%20example%20below.%20I%20would%20like%20to%20create%20a%20formula%20that%20evenly%20allocates%20the%20cost%20charged%20at%20the%20central%20location%20to%20the%20states%20within%20each%20project.%20For%20example%20in%20project%20C%20150k%20will%20be%20evenly%20allocated%20to%20MA%20%26amp%3B%20KY.%20Can%20anyone%20help%3F%3C%2FP%3E%3CTABLE%20width%3D%22207%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2259.4px%22%3EProject%20%23%3C%2FTD%3E%3CTD%20width%3D%2270.8333px%22%3ELocation%3C%2FTD%3E%3CTD%20width%3D%2286.6667px%22%3ECost%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2259.4px%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2270.8333px%22%3ECentral%3C%2FTD%3E%3CTD%20width%3D%2286.6667px%22%3E%26nbsp%3B%20200%2C000.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2259.4px%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2270.8333px%22%3ENY%3C%2FTD%3E%3CTD%20width%3D%2286.6667px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2259.4px%22%3EB%3C%2FTD%3E%3CTD%20width%3D%2270.8333px%22%3ECentral%3C%2FTD%3E%3CTD%20width%3D%2286.6667px%22%3E%26nbsp%3B%20300%2C000.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2259.4px%22%3EB%3C%2FTD%3E%3CTD%20width%3D%2270.8333px%22%3ELA%3C%2FTD%3E%3CTD%20width%3D%2286.6667px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2259.4px%22%3EC%3C%2FTD%3E%3CTD%20width%3D%2270.8333px%22%3ECentral%3C%2FTD%3E%3CTD%20width%3D%2286.6667px%22%3E%26nbsp%3B%20300%2C000.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2259.4px%22%3EC%3C%2FTD%3E%3CTD%20width%3D%2270.8333px%22%3EMA%3C%2FTD%3E%3CTD%20width%3D%2286.6667px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2259.4px%22%3EC%3C%2FTD%3E%3CTD%20width%3D%2270.8333px%22%3EKY%3C%2FTD%3E%3CTD%20width%3D%2286.6667px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2259.4px%22%3ED%3C%2FTD%3E%3CTD%20width%3D%2270.8333px%22%3ECentral%3C%2FTD%3E%3CTD%20width%3D%2286.6667px%22%3E%26nbsp%3B%20150%2C000.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2259.4px%22%3EE%3C%2FTD%3E%3CTD%20width%3D%2270.8333px%22%3ECentral%3C%2FTD%3E%3CTD%20width%3D%2286.6667px%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%2010%2C000.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2259.4px%22%3EE%3C%2FTD%3E%3CTD%20width%3D%2270.8333px%22%3EWY%3C%2FTD%3E%3CTD%20width%3D%2286.6667px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2259.4px%22%3EE%3C%2FTD%3E%3CTD%20width%3D%2270.8333px%22%3EIL%3C%2FTD%3E%3CTD%20width%3D%2286.6667px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2259.4px%22%3EE%3C%2FTD%3E%3CTD%20width%3D%2270.8333px%22%3ECA%3C%2FTD%3E%3CTD%20width%3D%2286.6667px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2251390%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2251408%22%20slang%3D%22en-US%22%3ERe%3A%20Allocating%20cost%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2251408%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F813299%22%20target%3D%22_blank%22%3E%40dw700d%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20I%20understand%20your%20request%20correctly%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0262.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F269202i96CAAC3DED08D252%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0262.png%22%20alt%3D%22S0262.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20formula%20in%20D2%2C%20confirmed%20with%20Ctrl%2BShift%2BEnter%2C%20is%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(B2%3D%22Central%22%2C%22%22%2CINDEX(C%3AC%2CMATCH(1%2C(A%3AA%3DA2)*(B%3AB%3D%22Central%22)%2C0))%2F(COUNTIF(A%3AA%2CA2)-1))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eand%20this%20has%20been%20filled%20down.%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Good day

The table below shows a project # and in certain cases  multiple locations per project. The cost for each project is charged to "Central" Location as shown in the example below. I would like to create a formula that evenly allocates the cost charged at the central location to the states within each project. For example in project C 150k will be evenly allocated to MA & KY. Can anyone help?

Project #LocationCost
ACentral  200,000.00
ANY0
BCentral  300,000.00
BLA0
CCentral  300,000.00
CMA0
CKY0
DCentral  150,000.00
ECentral    10,000.00
EWY0
EIL0
ECA0
1 Reply
best response confirmed by dw700d (Occasional Contributor)
Solution

@dw700d 

If I understand your request correctly:

 

S0262.png

 

The formula in D2, confirmed with Ctrl+Shift+Enter, is

 

=IF(B2="Central","",INDEX(C:C,MATCH(1,(A:A=A2)*(B:B="Central"),0))/(COUNTIF(A:A,A2)-1))

 

and this has been filled down.