Return value based on name combine score if same name

[SVTF]

Hello Excel Peeps - This is for excel 2010.

I attached test workbook is working as needed with kudos going out to @Ace_XL and @nflsales (Thanks for getting me up and running)

However at the time @nflsales asked me a question

In your sheet you have multiple locations for some advisors in this case what is you expected results

When I answered him I had forgotten that there are times when Advisors could move locations through out the year.

So I need a small tweak or two to the formulas in this workbook to accommodate the situation when an advisor moves locations.

In the test book I have added more info to explain expected result when an advisor moves locations.

See blue box on cases tab first
Then see blue box(s) on Advisor NPS tab.

Let me know if you have any questions to help resolve.

And Thank You in advance.

[NBVC]

My suggestion would be to add a helper formula in Cases sheet:

In E2:

=IF(COUNTIFS($B$2:$B2,$B2,$D$2:$D2,$D2)=1,COUNT(E$1:E1)+1,"")

copied down.

You can add a new Dynamic Named Range for this called: Helper and with formula: =Cases!$E$2:INDEX(Cases!$E:$E,COUNTA(Cases!$D:$D))

Then formula in B6 of the Advisor NPS would change to:

=IFERROR(INDEX(Advisor,MATCH(ROWS($B$2:$B2),Helper,0)),"")


And each of the formulas in columns C:E in the Advisor NPS tab, add the condition: *(Location=$A6)

e.g. in C6:

=SUMPRODUCT((Interview_Date>=TEXT($A$4,"yyyymmdd")*1)*(Interview_Date<=TEXT($B$4,"yyyymmdd")*1)*(Advisor=$B6)*(Location=$A6)*(LTR="Extremely Likely"))

and formula in G6 could become:

=((C6-E6)/(C6+D6+E6))/COUNTIFS(A:A,A6,B:B,B6)

copied down

By the way, you can get rid of all the #N/A errors in this sheet by adding an IF() check to each formula in column A, C:G checking if B is blank,

e.g. in A6:

=IF($B6="","",INDEX(Cases!B:B,MATCH(B6,Cases!D:D,0)))

[Ace_XL]

See if the attached helps

[SVTF]

Almost there...

@NBVC thanks for the suggestion - rep sent.

@Ace_XL - thanks for the suggestion - rep sent.

I did use @Ace_XL suggestion as it appeared to be a cleaner fix.

But still need to figure out the column G formula to return the real result for Bill B based on being in two locations.
The suggestion @NBVC made did not return the desired result.

See workbook - and blue box for expected result

Also is there a way to hide #Div/0! until a value is populated?

[NBVC]

Here is my attachment... where does it go wrong?

[Ace_XL]

In G6

Please Login or Register  to view this content.

Copy down

[SVTF]

@NBVC did not return the desired result of -25 based on the 2nd workbook I posted.
@Ace_XL that is one long a** formula LOL ... but it appears to be working.

I will test it in the real data document and if works will mark this thread as solved.

Thank You to both of you for your time.

Stay tuned ... rep sent again to each.