Looking for a simple formula that can count sales and half sales based on text in a column

[Paradigmgaming911]

Hello

Looking for a formula that can count sales for my sales staff on a spreadsheet where I need to also include 'split' deals, where each sales person gets a half sale instead of a full sale.

In the attached example, I used a simple COUNTIF formula to count the sales for each sales person, but I lack the understanding of how to create the formula to count full unit sales if there is nothing in the box in column E and to count 1/2 unit sales if there is a sales person listed in both column D and E.

On the attached example sheet, James' total sales should be 2.5 because he split the deal with Stephanie on row 7, and Stephanie's total unit count would be 3.5 because she had 3 full deals and one split deal.

Thanks!


Split Deals example.xlsx

[JohnTopley]

Try

=COUNTIFS($D:$D, $I7,$E:$E,"")+(COUNTIFS($D:$D,$I7,E:E,"<>")+COUNTIFS(E:E,$I7))*0.5

Stehanie had 2 split deals and 3 full.

[Paradigmgaming911]

Try

=COUNTIFS($D:$D, $I7,$E:$E,"")+(COUNTIFS($D:$D,$I7,E:E,"<>")+COUNTIFS(E:E,$I7))*0.5

Stehanie had 2 split deals and 3 full.

Amazing! Thank you ... and yes, I can't count