Need help with formula

Occasional Contributor

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!




3 Replies


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 




in cells V5:X5 and copy them down.

Result for ACCT_MGR_EMPLY_ID 13637 would be 3,42.

Not sure I follow unfortunately


EDIT: I needed to anchor cells A5 and D5. Looks like everything works now.

best response confirmed by dmoney7612 (Occasional Contributor)


Unfortunately i missed to anchor these cells. If you want to exclude zero based accounts you can replace countif formula by:
This formula returns no of accounts without zero. 1 account for sales rep 1772 and 10 accounts for sales rep 13637 and so on.