+ Reply to Thread
Results 1 to 2 of 2

How to identify the cells from the sumproduct formula result

  1. #1
    Registered User
    Join Date
    05-29-2018
    Location
    Florida
    MS-Off Ver
    Excel 2013
    Posts
    1

    Question How to identify the cells from the sumproduct formula result

    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.

    I have a Sumproduct formula with multiple criteria that helps identify the number of issues I have on the main spread sheet. 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?

    To further explain my intention, the main tab on my spread sheet 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 am 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 a new tab, I used several formulas (like the one below) to determine the number of different exceptions we need to catch, however I am wondering if there is a way to also identify these specific cells that the exceptions fall in so that the user can immediately locate and correct it.
    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=""))

    Note: I have also tried to achieve this by using conditional formatting using the formula above, however the issue that I run into with that approach is that the entire row gets highlighted instead of the specific cells matching the criteria from the formula. I am open to a solution with this as well if it is an easier approach.

    I hope I am getting across what I am trying to do! Thank you in advance to whomever can help!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to identify the cells from the sumproduct formula result

    Hi, welcome to the forum

    Maybe another approach would be to have a helper column that ID's the rows with the exception/s (using a small modification to the formulas you already developed to show 1/0 or x/"" or whatever works), and then just filter on the helper?

    If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. I don't understand SUMPRODUCT formula result
    By Dark0Prince in forum Excel General
    Replies: 4
    Last Post: 02-17-2017, 06:39 PM
  2. [SOLVED] Identify the highest value over a range of cells and classify the result
    By MTC2016 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-08-2017, 01:24 PM
  3. Combining Sumproduct Result for Three or More Cells
    By Buddy8 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-26-2016, 09:15 AM
  4. sumproduct formula result incorrect
    By bobraney in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-08-2014, 11:19 PM
  5. [SOLVED] Formula SUMPRODUCT works, now needs some tweaking, to True/False identify TEXT
    By steve08087 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-29-2013, 06:20 PM
  6. Why sumproduct formula return result 0
    By ARIA1 in forum Excel General
    Replies: 16
    Last Post: 05-19-2012, 10:47 AM
  7. SUMPRODUCT: can sum a cell formula result?
    By matrix_qw in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-25-2008, 07:49 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1