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 on...
=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.
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.