+ Reply to Thread
Results 1 to 2 of 2

SUMPRODUCT. Exact match VS. Search

  1. #1
    Registered User
    Join Date
    12-12-2014
    Location
    NYC
    MS-Off Ver
    2010
    Posts
    43

    SUMPRODUCT. Exact match VS. Search

    Hi! I've got some tricky problem.

    Let's say, I have this kind of table:
    Capture.PNG

    What I need is to sum all 1's if they have something to do with sociology and have 'high' in A:A.
    The problem is twofold: 1) I do not need to count the value in B2, since 'sociology' is already mentioned in C2. But I need to count B3, since sociology is mentioned only here.
    2) I need to do this using SUMPRODUCT.

    So in the end, I would like to get 2, and not 3. I thought I could at first sum all the 1's, when the column title does not match "sociology" exactly, but contains "sociology" as a part of it. Then I would sum all 1's, when the title matches "sociology" exactly and there are no cases when "1" exists in columns which have "sociology" in their titles. And then I would sum the first and the second.

    The first part seems to be easy:
    =SUMPRODUCT(($B$2:$D$4=1)*($B$1:$D$1<>"sociology")*(ISNUMBER(SEARCH("sociology",$B$1:$D$1)))*($A$2:$A$4="high"))
    This would result in "1", which is expected. This would count only C2.

    The second part I thought should have been something like this:
    =SUMPRODUCT(($B$2:$D$4=1)*($B$1:$D$1="sociology")*(NOT(ISNUMBER(SEARCH("sociology ",$B$1:$D$1))))*($A$2:$A$4="high"))
    This does not work as intended, since here I get "2", since it counts both B2 and B3:
    =SUMPRODUCT(($B$2:$D$4=1)*({TRUE,FALSE,FALSE})*({TRUE,FALSE,FALSE})*($A$2:$A$4="high"))

    The question is how should I make the second part work just like the first and to count only B3.

    Thank you very much in advance!
    Last edited by TestMailinator; 05-26-2015 at 11:43 AM.

  2. #2
    Registered User
    Join Date
    12-12-2014
    Location
    NYC
    MS-Off Ver
    2010
    Posts
    43

    Re: SUMPRODUCT. Exact match VS. Search

    To sum it up even shorter, the question is this. Is there a way to count all 1's in B:B, if and only if there are no 1's in C:D, by using SUMPRODUCT and column titles as criteria?

+ 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. [SOLVED] SEARCH and EXACT match
    By bibu in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-12-2014, 06:07 PM
  2. Search for exact match column header
    By ks100 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-13-2014, 01:21 PM
  3. Replies: 6
    Last Post: 01-28-2012, 06:59 PM
  4. Replies: 2
    Last Post: 01-28-2012, 05:26 PM
  5. SUBPRODUCT and String search functions
    By patrickj in forum Excel General
    Replies: 1
    Last Post: 10-16-2009, 11:08 AM

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