If cell contains two values, count each value as .5

I have a sheet that calculates number of real estate listings each agent has taken. However, sometimes, 2 agents will split a listing. When this happens, the listing is counted as 1 but each person listed is counted as .5 within the cell. So if a cell contains 2 of my chosen drop down menu options, how can I get the cell to count as  1 but .5 for one person and .5 for the other? The cell itself should count as 1, but each agent mentioned should be counted as .5 in their respective formulas, but only if 2 names are in the cell. I have the following formulas set up:

Formula counting Total listings in the office: =COUNTIF(E3:E300,“*”)

Formula counting each Agent: =COUNTIF(E3:E221,“*Renetta*”)

=COUNTIF(E3:E221,“*Craig*”)

 

Let me know if I am being clear.

By: Robin

Leave a Reply

Your email address will not be published. Required fields are marked *