Performing an Operation with Lists of Differing Length with Duplicate and Unique Values

--For the purpose of this example I will use the example of # of Red Cars and # of Blue Cars--

In the A column: a list of a county and its respective state. (Example: Adams County, IL)
In the B column: a count of the number of Red Cars (my data) in that county (Example: 3000)
In the C column: a shorter list of counties (Column A contains all of the counties in Column C, but A has many additional counties since for the purpose of this example, the additional counties of Column A have no blue cars) and their respective state.
In the D column: a count of the number of Blue cars in that county.

Since Column A has additional counties, the counties do not line up, so I cannot perform a simple operation without doing something first. Also there are far too many values to do it manually. I would like to take the (Column B value associated with CountyX)/(Column D value associated with the same CountyX) and if there is not a Column D value to divide by (since there are more counties in column A) to be able easily separate those.

Below is an example from the excel sheet:
hC64Q8s.png

Thank you so much!