Forum Discussion
Solved
Countifs Partial Values
Hello!
I have several salespeople and I am trying to calculate their monthly sales volume. So for instance, I have a column A1:A100 with their names. I have the proper formula to count those. The only issue is that, at times, they split a sale. So while A1:A4 might only have one name, A5 may have two. How can I count that cell as a half value for each person?
Like below here, John sold 1.5 and Kevin sold 3, but the simple countif I am using shows John at 2 and Kevin at 4.
| John |
| Stacey |
| Kevin |
| Kevin |
| Alice |
| Jason |
| Mark, Kevin |
| Jason |
| Kevin, John |
=SUM(IF(C1=$A$1:$A$9,1,IF(ISNUMBER(SEARCH(C1,$A$1:$A$9)),0.5,0)))This formula works in my sample file. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.
4 Replies
Using the same logic, would it be possible to sum the gross for their deals and split that when appropriate if those were listed in a separate column?
Wonderful! Thank you!
This is what I ended up with that worked perfect!
If I wanted to create another column to calculate their sales gross total, which is listed in another column and would have to be full or half value based upon that information from A1:A9, would that be possible?
For example, if column E contained gross for every specific deal, could it go and sum the gross in column E on any of say John's deals, but also give him half the value for the one he split with Kevin?
=SUM(IF(C1=$A$1:$A$9,1*$E$1:$E$9,IF(ISNUMBER(SEARCH(C1,$A$1:$A$9)),0.5*$E$1:$E$9,0)))This formula returns the expected result in my sample sheet.
=SUM(IF(C1=$A$1:$A$9,1,IF(ISNUMBER(SEARCH(C1,$A$1:$A$9)),0.5,0)))This formula works in my sample file. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.
