How to determine cell contents of the sumproduct result

[CachimJ]

Hello!

I would first like to apologize if this question has already been posted and answered numerous times but I was unable to find the right wording for my question to find a thread that matched.

In short, I have a sumproduct formula with multiple criteria that helps identify the number of issues I have on the main spreadsheet. I got the number of issues, however now I would like to identify the cells meeting this criteria. Is there any way to do this?

For example: 2 issues identified; B10 and B26. (Or more specifically, if possible, the contents of that given cell?)

Sumproduct:
=SUMPRODUCT(--(May!C2:C452="FHA"),--(May!Z2:Z452<>""),--(May!AB2:AB452<>""),--(May!AC2:AC452=""))

I hope this makes sense! Thank you in advance to whomever can help!

 

[Joe4]

Could you possibly use Data Filters to only show the records meeting your criteria?
It seems to me that they may be the easiest/fastest method to see that data.

 

[CachimJ]

Hi Joe4, thanks for your reply!

My main tab on the spreadsheet is a report with many different columns that would need to be filtered several different ways each time in order to catch the exceptions we are looking for. I was trying to avoid this manual process by creating a new tab to show these exceptions without having to look for them and leave cause for any user/human error where something could potentially be missed. In the new tab, I was already able to pull the total number of the different exceptions we need to look for, however I wanted to see if there would be a way to also identify which row or cell this exception falls in so that the user can immediately find and correct it. I hope I am getting across what I am trying to do!

 

[Joe4]

What about using something like Conditional Formatting to highlight the rows meeting your criteria?