I am trying to count the unique number of times the name of a client appears in a worksheet based on the specific primary sales rep and secondary sales rep who is assigned to the account, as well as a date range.
Below is an attached excel file offering an outline of what I am trying to solve.
Thanks in advance
Last edited by Perk1961; 06-22-2016 at 10:57 AM. Reason: I was able to attach my excel sample file.
I have a solution (see Sheet2) that uses a pivot table trick. First it makes a "composite key" of the factors that make a record unique. Then what it does is take the inverse of the count of how many times that composite key appears. So if there are two of the same it's 1/2, if there are three of the same, it's 1/3, etc. This sets it up so that when the pivot table sums them up, they add back up to 1 and are only counted as one.
So Craig has two entries for ABC Company where there is no backup sales rep. We only want to count these two records once, so they each get a half value.
Then it's a matter of putting the information into the pivot table.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
Thank you. I was actually looking to use a formula that did not involve Pivot tables because I need to ultimately incorporate it into a Google Sheets. I was thinking a SumProduct with and And solution could work, but not sure how to write it.The other is perhaps a CountIf formula with an AND solution.
Attached is an revised excel clarifying the problem at hand.
Thanks in advance.
Try this for unique companies by date in D14.
Formula:
Please Login or Register to view this content.
Edit And this in C18:C20
Formula:Please Login or Register to view this content.
Last edited by FlameRetired; 06-22-2016 at 02:34 PM.
Dave
This is much closer. I just need the total count to include where the rep appears as a primary and backup sales rep. As such, the answer for Craig should be 4, joe is 3 and Mark is 2.
I am also looking to include in this same formula a date range so the results will take that into consideration. I hope that makes sense.
Got it.
Try array entering this in C18 and filling down.If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Formula:Please Login or Register to view this content.
Fantastic! Thank you so much!
Last edited by Perk1961; 06-22-2016 at 03:37 PM. Reason: spelling error on my part.
You are welcome. Thanks for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks