SOLVED

Need help with formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2838104%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2838104%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20Everyone-%20I%20have%20a%20file%20here%20with%20sales%20reps%20which%20includes%20their%20accounts%20and%20number%20of%20products%20in%20each%20account.%20I%20am%20trying%20to%20determine%20the%20average%20number%20of%20products%20(columns%20F%2FG%20%26amp%3B%20N%2FO)%20per%20rep.%20As%20you%20can%20see%2C%20there%20are%20many%20reps%20and%20each%20have%20varying%20totals%20so%20I%20was%20having%20issues%20coming%20up%20with%20a%20formula%20that%20calculates%20the%20average%20for%20each%20rep%20and%20also%20excludes%20zero%20based%20accounts.%20Was%20thinking%20a%20SUMIF%20or%20COUNTIF%20but%20wasn't%20totally%20sure.%20Appreciate%20any%20assistance...thanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22dmoney7612_0-1634048496571.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F316723i528387B78FE39A15%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22dmoney7612_0-1634048496571.png%22%20alt%3D%22dmoney7612_0-1634048496571.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2838104%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2838403%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2838403%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1019927%22%20target%3D%22_blank%22%3E%40dmoney7612%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20i%20correctly%20understand%20what%20you%20want%20to%20do%20i%20would%20suggest%20to%20set%20up%20a%20unique%20list%20of%26nbsp%3BACCT_MGR_EMPLY_ID%2C%20for%20example%20in%20range%20U5%3AU416%20and%20then%20to%20apply%20formulas%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMMEWENN(A5%3A%24A%2453437%3BU5%3BD5%3A%24D%2453437)%3C%2FP%3E%3CP%3E%3DZ%C3%84HLENWENN(A5%3A%24A%2453437%3BU5)%3C%2FP%3E%3CP%3E%3DV5%2FW5%3C%2FP%3E%3CP%3Ein%20cells%20V5%3AX5%20and%20copy%20them%20down.%3C%2FP%3E%3CP%3EResult%20for%26nbsp%3BACCT_MGR_EMPLY_ID%2013637%20would%20be%203%2C42.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2838410%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2838410%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1019927%22%20target%3D%22_blank%22%3E%40dmoney7612%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20i%20correctly%20understand%20what%20you%20want%20to%20do%20i%20would%20suggest%20to%20set%20up%20a%20unique%20list%20of%26nbsp%3BACCT_MGR_EMPLY_ID%2C%20for%20example%20in%20range%20U5%3AU416%20and%20then%20to%20apply%20formulas%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMIF(A5%3A%24A%2453437%2CU5%2CD5%3A%24D%2453437)%3C%2FP%3E%3CP%3E%3DCOUNTIF(A5%3A%24A%2453437%2CU5)%3C%2FP%3E%3CP%3E%3DV5%2FW5%3C%2FP%3E%3CP%3Ein%20cells%20V5%3AX5%20and%20copy%20them%20down.%3C%2FP%3E%3CP%3EResult%20for%26nbsp%3BACCT_MGR_EMPLY_ID%2013637%20would%20be%203%2C42.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2838421%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2838421%22%20slang%3D%22en-US%22%3E%3CP%3ENot%20sure%20I%20follow%20unfortunately%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEDIT%3A%20I%20needed%20to%20anchor%20cells%20A5%20and%20D5.%20Looks%20like%20everything%20works%20now.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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!

 

dmoney7612_0-1634048496571.png

 

3 Replies

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

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