Need help with formula

[dmoney7612]

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!

[#2]

@dmoney7612 

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.

[#3]

Not sure I follow unfortunately

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

[#4 · Accepted solution]

@dmoney7612 

Unfortunately 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.