Good afternoon excel community,
Every disposition a rep makes, including non dispositions, is included in raw data I receive but on dispositions where a sale is made there is a '1' in the Z column.
I'm trying to create a formula where I only count the first sale (sold) of each day (P Column) for each sales rep (G Column), if they made at least 1 sale of course. I also would like to create a formula in a second column where I count the # of reps who made more than 1 sale.
To count the first sale I'm using the formula =IF(COUNTIFS($P$3:P3,P3,$G$3:G3,G3,$Z$3:Z3,Z3)=1,SUMIFS($Z$3:Z3,$P$3:P3,P3,$G$3:G3,G3),"") and filling down to the end of the data. I think I'm on the right track with that but let me know if there is an error in the logic.
If this works, I'm thinking =IF(COUNTIFS($P$3:P9,P9,$G$3:G9,G9,$Z$3:Z9,Z9)=2,SUMIFS($Z$3:Z9,$P$3:P9,P9,$G$3:G9,G9),"") should work but I'm getting outputs of 1 and 2 in that column when I only want an output of 1 on that 2nd sale and 0 for everything else. In some instances I'm getting 1 or 2 in the 'Distinct Reps With 2+ Sales/Day' column when there isn't a 1 in the 'Distinct Reps With Sales/Day' column or 'Sold' column so I know my logic is off. Any assistance would be greatly appreciated
MTD Rep Participation.PNG
Bookmarks