+ Reply to Thread
Results 1 to 5 of 5

SUMPRODUCT. using AND-OR logic with text

  1. #1
    Registered User
    Join Date
    05-15-2013
    Location
    Philadelphia, USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    SUMPRODUCT. using AND-OR logic with text

    Hello,

    I am struggling with a SUMPRODUCT formula that essentially "ANDs" a criteria with multiple "OR" criteria to produce a result. The "OR" is more than one text strings that may exists in a range. I have not been able to make the formula work. I hope someone can point out what is wrong and can provide some explanation on how to correct it or if there is a better way to do this.

    To better explain the problem and the result I desire, I have attached a sample Excel file. Column A contains text in each row. Corresponding to each text is a date in Column B.

    Cell B11 contains the formula. I first compare the date range (B4:B8) with a threshold date in B1. This produces an array {1,0,1,0,0} where Cell B4 and B6 match the threshold date. I then search column A from A4:A8 looking for the text "CAB" OR "VIC". The second array of the SUMPRODUCT should produce a result {1,0,1,1,0} (which it does not). My thought was when the second array is "ANDED" with the first (date) array, the resulting array would be {1,0,1,0,0}. The sum of this would be 2. Instead, I get a value error.

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: SUMPRODUCT. using AND-OR logic with text

    Try this...

    =SUM(COUNTIFS(A4:A8,{"CAB*","VIC*"},B4:B8,B1))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMPRODUCT. using AND-OR logic with text

    Try

    =SUMPRODUCT((B4:B8=B1)*(LEFT(A4:A8,3)={"CAB","VIC"}))

  4. #4
    Registered User
    Join Date
    05-15-2013
    Location
    Philadelphia, USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: SUMPRODUCT. using AND-OR logic with text

    Thank you Tony and Jonmo.

    The SUMPRODUCT formula worked. I also see the mistake I made in my formula.

    The SUM(COUNTIFS(---)) looks like a neat way to accomplish what I was trying. I tried it and it works too. I can't use it because of Excel 2003 that is still in use at my place.
    Last edited by FAL22; 05-26-2013 at 04:25 PM.

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: SUMPRODUCT. using AND-OR logic with text

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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