Hello,
Please see the attached spreadsheet. In the spreadsheet you will see that I have 2 tabs, one summary, one detail. I am trying to create a formula on the summary page for "Total Accounts". I need to be able to count how many accounts each rep has based on the data in the detail tab. So for instance Rep 1 should have 2 accounts, while the rest should only have 1 account. There will be multiple entries for each account but for my purposes I only need to count each account once.
Any thoughts would be greatly appreciated.
Thanks in advance,
Neil
Portfolio Management Report Template.xlsx
Welcome to the Forum!
That is exactly what your existing formula produces. What do you need in column B that is different than what's in column C?Originally Posted by Neil2470
Rep Name Total Accounts Total Worked ( at least 1 attempt to) Rep 1 2 Rep 2 1 Rep 3 1 Rep 4 1 Rep 5 1
Making the world a better place one fret at a time
||||||
If someone helped you, please click on the star icon at the bottom of their post
If your problem is solved, please update the first post:
EDIT, Go Advanced button, set Prefix to SOLVED
[code]
' Enclose code in tags like this
[/code]
Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.
Let me know if this helps, paste this formula in cell B2 and copy the same down.
=COUNTIFS(Details!$A$1:$A$22,Summary!$A2,Details!$D$1:$D$22,"Engagement")
If you are looking to count the unique Accounts based on each Rep. try this...
=SUM(IF(FREQUENCY(IFERROR(IF(Details!A$2:A$22=A2,MATCH(Details!B$2:B$22,Details!B$2:B$22,0)),""),ROW (Details!A$2:A$22)-ROW(Details!A$2)+1),1))
Confirmed with CTRL+SHIFT+ENTER, copy down.
HTH; Haseeb
If your problem is solved, please say so clearly, and mark your thread as Solved:
Forum Rules & How to Mark a thread as SOLVED
Thank you all for your responses. I am trying to get exactly what Haseeb A said "unique accounts based on each rep". Haseeb you are in the ballpark, but I was not able to get your formula to work. Did you actually try it within my spreadsheet and have success?
Thanks,
Neil
Last edited by Neil2470; 01-05-2012 at 02:56 PM.
Thank you all for your responses. I am trying to get exactly what Haseeb A said "unique accounts based on each rep". Haseeb you are in the ballpark, but I was not able to get your formula to work. Did you actually try it within my spreadsheet and have success?
Thanks,
Neil
Last edited by Neil2470; 01-05-2012 at 02:56 PM.
This is an Array Formula must hit CTRL+SHIFT+ENTER, rather than just ENTER.
See the attached.
HTH; Haseeb
If your problem is solved, please say so clearly, and mark your thread as Solved:
Forum Rules & How to Mark a thread as SOLVED
Thank you Haseeb. I am still new to array formulas but this was exactly what I needed.
FWIW, if you prefer to avoid the Array entry requirement you can use the below:
no more efficient than the Array however (arguably less so in this instance)B2: =SUMPRODUCT((Details!$A$2:$A$22=$A2)/COUNTIFS(Details!$A$2:$A$22,Details!$A$2:$A$22&"",Details!$B$2:$B$22,Details!$B$2:$B$22&"")) confirmed with Enter copied down
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thanks DonkeyOte, that's great as well
Thank you all for helping me out. I do have another problem I am trying to figure out.
In the attached file, on the Summary tab I am trying to figure out how many times a specified account shows up in the details tab along with which rep it belongs too. For instance rep 1 has called 2 accounts at least 5 times. I need the formula to show how many Rep 1 accounts have been called at least 5 times. (So to simplify, I need a count of how many account entries show up on the details tab belonging to the specified rep) The formula should only use columns A and B.
I am not sure I fully understood. Based on the sample,
Rep 1 has two Accounts 'Train Inc' & 'TIDE CO'. 'Train Inc' occur 7 times, 2nd one 2 times. So if you are trying to count an account occur >=5 times for a rep, try this
Highlighted is the change.=SUM(IF(FREQUENCY(IFERROR(IF(Details!A$2:A$22=A2,MATCH(Details!B$2:B$22,Details!B$2:B$22,0)),""),ROW(Details!A$2:A$22)-ROW(Details!A$2)+1)>=5,1))
HTH; Haseeb
If your problem is solved, please say so clearly, and mark your thread as Solved:
Forum Rules & How to Mark a thread as SOLVED
Haseeb, once again you have provided exactly what I needed. Thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks