Performing calculations on data that isn't duplicated

[LWillbourn]

I am currently in the process of building a prospect tracking document for the company I work for.


I have built an input sheet with various columns including the prospect's name and whether the prospect is hot or not (denoted by a Y or a N). As the sales process is through stages I have multiple entries per person (i.e. a single person will need to go through the first stage then the second stage etc.), thus I have duplicate people with multiple Y in the hot or not column.


I have the formula to get the number of unique people =SUMPRODUCT(('Input Sheet'!$L$9:$L$1081<>"")/COUNTIF('Input Sheet'!$L$9:$L$1081,'Input Sheet'!$L$9:$L$1081&""))


But now want to get a count of out of the unique people how many are hot (so have a Y in the corresponding column) and seem to be struggling with it.


Any help on how to do this would be greatly appreciated


Thanks,


Luke

 

[Luke M]

Greetings!

I think this is doable if you use 1 helper column. Helper column concatenates person's name with the hot/not column. Something like:

=L9&M9


Assuming the helper column is col N, the counting formula becomes:

=SUMPRODUCT((RIGHT('Input Sheet'!$N$9:$N$1081)="Y")/(COUNTIF('Input Sheet'!$N$9:$N$1081,'Input Sheet'!$N$9:$N$1081&"")))

 

[LWillbourn]

Thanks for your help, that's fantastic and appears to work!


If I were to want to include another another qualifying factor in there (another column) i.e. what kind of prospect they were (whether they were a decision maker etc.) how would the formula look? Would I make another helper column including such as =L9&M9&S9 and how would this make the forumla look?


Again many thanks for your help.


Luke

 

[Luke M]

That sounds like it would work. If the next factor uses a similar method of marking, eg a "Y" in the column, your helper column would use the formula you specified, and the counting formula would be:

=SUMPRODUCT((RIGHT('Input Sheet'!$N$9:$N$1081,2)="YY")/(COUNTIF('Input Sheet'!$N$9:$N$1081,'Input Sheet'!$N$9:$N$1081&"")))

 

[LWillbourn]

So having made the helper column with 3 pieces of information added together =L9&W9&S9 (name&Y&Decision Maker) and have added the formula


=SUMPRODUCT((RIGHT('Input Sheet'!$Y$9:$Y$1081,2)="YDecision Maker")/(COUNTIF('Input Sheet'!$Y$9:$Y$1081,'Input Sheet'!$Y$9:$Y$1081&"")))


and it doesn't seem to work, only giving me the answer of 0.


Any ideas why this may not be working?


Thanks,


Luke

 

[Luke M]

This bit of formula:

RIGHT('Input Sheet'!$Y$9:$Y$1081,2)

Is telling the function how long of a string to look at, and then compare with next bit. Currently, it's only grabbing the last 2 letters. Since we're looking for "YDecision Maker", and I'm feeling lazy and don't want to count it out myself, we can do this:


=SUMPRODUCT((RIGHT('Input Sheet'!$Y$9:$Y$1081,LEN("YDecision Maker"))="YDecision Maker")/(COUNTIF('Input Sheet'!$Y$9:$Y$1081,'Input Sheet'!$Y$9:$Y$1081&"")))

 

[LWillbourn]

Amazing, that is very useful to know as I had a similar problem with helper columns before and ended up resorting to using array formulas to do what I needed to do.


I have one last question on this topic before it is closed


If I wanted to pull out the names of the unique people that were hot how would I go about this?


Many thanks,


Luke