Determining a value to a cell with potential multiple options

**allnet000** · 05-06-2011, 11:38 AM

I am working on a project in which I need to determine the number of possible residents can attend a particular school. The issue I am running into is when a zip code has multiple school options. In those cases the number of residents will be divided amongst the number of schools. Example - 2,000 residents - 4 schools - each would have 500 next to their name in the summary section featured below the data. There are occurrences where it can be up to 7 potential schools for a particular zip code. I have attached an example of what I am trying to accomplish. Thank you.

**NBVC** · 05-06-2011, 12:47 PM

Try:

=SUMPRODUCT(($D$6:$F$11=A15)*($B$6:$B$11))/COUNTIF($D$6:$F$11,A15)

copied down.

**allnet000** · 05-06-2011, 01:50 PM

Thank you. The formula provided is not accurately counting if the number of residents is divided by multiple schools. The Smith School appears in Rows 6 & 11. Row 6 has 7,135 residents with 2 schools, therefore each school would receive 3,567.5 and in Row 11 there are 13,068 residents with 3 schools so each should would receive 4,356. Smith School total residents should equal 7,923.5. Using the formula in the previous post Smith School's total is 10,101.5.