Oct 12 2021 07:24 AM - edited Oct 12 2021 07:54 AM
Hey Everyone- I have a file here with sales reps which includes their accounts and number of products in each account. I am trying to determine the average number of products (columns F/G & N/O) per rep. As you can see, there are many reps and each have varying totals so I was having issues coming up with a formula that calculates the average for each rep and also excludes zero based accounts. Was thinking a SUMIF or COUNTIF but wasn't totally sure. Appreciate any assistance...thanks!
Oct 12 2021 08:29 AM
If i correctly understand what you want to do i would suggest to set up a unique list of ACCT_MGR_EMPLY_ID, for example in range U5:U416 and then to apply formulas
=SUMIF(A5:$A$53437,U5,D5:$D$53437)
=COUNTIF(A5:$A$53437,U5)
=V5/W5
in cells V5:X5 and copy them down.
Result for ACCT_MGR_EMPLY_ID 13637 would be 3,42.
Oct 12 2021 08:33 AM - edited Oct 12 2021 08:50 AM
Not sure I follow unfortunately
EDIT: I needed to anchor cells A5 and D5. Looks like everything works now.
Oct 12 2021 09:13 AM
SolutionUnfortunately i missed to anchor these cells. If you want to exclude zero based accounts you can replace countif formula by:
=SUMPRODUCT(($A$2:$A$53437=U5)*($D$2:$D$53437>0))
This formula returns no of accounts without zero. 1 account for sales rep 1772 and 10 accounts for sales rep 13637 and so on.
Oct 12 2021 09:13 AM
SolutionUnfortunately i missed to anchor these cells. If you want to exclude zero based accounts you can replace countif formula by:
=SUMPRODUCT(($A$2:$A$53437=U5)*($D$2:$D$53437>0))
This formula returns no of accounts without zero. 1 account for sales rep 1772 and 10 accounts for sales rep 13637 and so on.