+ Reply to Thread
Results 1 to 2 of 2

Sumproduct Isnumber Search is returning same result for different values

  1. #1
    Registered User
    Join Date
    11-06-2019
    Location
    Los Angeles
    MS-Off Ver
    1902
    Posts
    1

    Sumproduct Isnumber Search is returning same result for different values

    Hello - I utilizing a formula that is intended to count the number of visible entries in a table that meets multiple criteria utilizing Sumproduct, Isnumber, and Search. However; I have just realized that in some instances the formula is including results it should not.

    For example, in the below forumla, to be counted, the row needs to contain "X" in column (E), contain "F" in column (K), and contain "2" in column (H) - the formula should also only be counting visible results. However, it looks like if column (H) has the number "12" it is counting it. Similarly if I adjust the formula to search for "7" in column (H), it will count those that have "17".

    How can I adjust the formula to count only exact matches for "2" or "7" in column (H)?


    =SUMPRODUCT(SUBTOTAL(3,OFFSET($F$11:$F$23733,ROW($F$11:$F$23733)-MIN(ROW($F$11:$F$23733)),,1)),ISNUMBER(SEARCH("X",E11:E23733))+0,ISNUMBER(SEARCH("F",$K$11:$K$23733))+0,ISNUMBER(SEARCH("2",$H$11:$H$23733))+0)


    THANKS!

  2. #2
    Registered User
    Join Date
    12-03-2012
    Location
    CHENNAI , INDIA
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Sumproduct Isnumber Search is returning same result for different values

    Hi ,

    Try this :

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The changes are highlighted.

    This is an array formula , to be entered using CTRL SHIFT ENTER.

    Narayan
    Last edited by NARAYANK991; 11-07-2019 at 12:40 AM.

+ 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. IF AND ISNUMBER SEARCH Function. Keeps returning false value even if true.
    By joshuarobbins in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-05-2019, 02:53 AM
  2. [SOLVED] If isnumber search function returning false
    By hawkwolf in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-14-2018, 02:36 PM
  3. Replies: 14
    Last Post: 06-17-2013, 09:43 AM
  4. How many values can be in IF(ISNumber(search
    By aurness in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-25-2013, 11:11 PM
  5. Replies: 3
    Last Post: 09-12-2012, 04:58 AM
  6. [SOLVED] how to have multiple ISNUMBER search function in SUMPRODUCT
    By melvyndb in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-29-2012, 09:34 PM
  7. SUMPRODUCT((ISNUMBER(SEARCH() function
    By redneck joe in forum Excel General
    Replies: 13
    Last Post: 12-08-2006, 06:19 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