Allocating cost formula

[dw700d]

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 #	Location	Cost
A	Central	200,000.00
A	NY	0
B	Central	300,000.00
B	LA	0
C	Central	300,000.00
C	MA	0
C	KY	0
D	Central	150,000.00
E	Central	10,000.00
E	WY	0
E	IL	0
E	CA	0

[#2 · Accepted solution]

@dw700d 

If I understand your request correctly:

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.