Most Common text result in a selection If certain value present

[AMeloche]

Here's my sheet showing the data I'm trying to collect on a separate front table

Screen Shot 2015-06-18 at 5.51.08 PM.png

I'm trying to display the most common "Ad Source" for all calls placed to "Clinic Location" "ME"

Here's my formula
=IF(('PCC Call Data'!J3:J246,(INDEX('PCC Call Data'!B3:B246,MODE(IF(('PCC Call Data'!B3:B246<>"")*ISNA(MATCH('PCC Call Data'!B3:B246,$Z$466:$Z466,0)),MATCH('PCC Call Data'!B3:B246,'PCC Call Data'!B3:B246,0)))))),"")

I placed the "IF" statement at the front of the "INDEX" statement but I'm not getting a positive result. the INDEX statement functions well on its own, grabbing the most common result, but I can't expand it to include the IF statement for each different locations.

I'm looking for location-specific results for our inbound marketing campaigns.

Appreciate any help I can get.
Thanks!
Alex

[Kaper]

Probably main reason why it is not answered for quite a long time is ... no data to test, so:

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[AMeloche]

Universal Reporting upload.xlsx

actual sheet attached.

Hoping for any help at all.

Thanks for the ti[p on uploading

[Kaper]

I think that checking location shall go inside MODE part, so:

Please Login or Register  to view this content.

of course array formula, and changed addressing to absolute - othewise with copying down the referenced ranges in PCC Call data sheet would move

[AMeloche]

Very clever! I saw your reference to the clinic location matching the front page reference... very impressive. Thank you very much for this!

[AMeloche]

Kaper - using the same workbook I'd like to count the new patients on the PCC Call Data form (some are marked with "Y" and some are marked with "X" so it needs to me "" or "<>") for each individual location. Have you got a suggestion for this also?

Thank you for your help.

[Kaper]

In K4 on "Display Sheet":

Please Login or Register  to view this content.

and copy down

[AMeloche]

THANKS KAPER! !

Lastly, I want to (without a pivot table) be able to select a month's worth of data (using a dropdown list and an IF statement) and display the practice-specific data on the 'Display Sheet". The data would come from the 'PCC Call Data' sheet.

Example: Select "June" from dropdown list and the practice-specific data on the 'Display Sheet' will show the data only for June (the months are separated in the 'PCC Call Data' sheet)

I realize this is a large endeavour. Thank you for any possible he

[6StringJazzer]

Closed at AMeloche's request to re-open in Commercial Services.