+ Reply to Thread
Results 1 to 9 of 9

Sumproduct with OR Logic

  1. #1
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Sumproduct with OR Logic

    Hello peoples!

    I'm trying to count the number of rows in a dataset where column A is something, B is something, C is something, D is not something, D is not something else, and column E > 0 OR F is something but I just can't get the result I want.

    My actual dataset is a staggering 294,000 rows and 46 columns so here's a simplified version:
    Please Login or Register  to view this content.
    I've tried SUMPRODUCT((A2:A9="C")*((B2:B9>0)+(C2:C9="Helped"))) but this seems to double count where both criteria are met instead of just once.

    The output should be 6, not 7.

    sumproduct or.xlsx
    Last edited by daffodil11; 01-17-2014 at 07:10 PM.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,656

    Re: Sumproduct with OR Logic

    =SUMPRODUCT((A2:A9="C") * ((B2:B9<>0) + (C2:C9="Helped") > 0))
    Last edited by shg; 01-17-2014 at 07:51 PM.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Sumproduct with OR Logic

    Thank you. If I ever have children, I'm naming them all after you.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,656

    Re: Sumproduct with OR Logic

    You're welcome. Check with my daughters to avoid a namespace conflict.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,745

    Re: Sumproduct with OR Logic

    Surely you jest?
    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

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,656

    Re: Sumproduct with OR Logic

    First grandchild arrives in July.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,745

    Re: Sumproduct with OR Logic

    congrats!!!

    (you missed the reference to the movie, Airport...dont call me Shirley lol)

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,656

    Re: Sumproduct with OR Logic

    Airport? James Earl Jones, a thousand years ago?

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,745

    Re: Sumproduct with OR Logic

    hey now, shirely it wasnt even 500 years ago?

+ 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] SUMPRODUCT. using AND-OR logic with text
    By FAL22 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-26-2013, 04:34 PM
  2. SUMPRODUCT with multiple logic and without duplicates
    By cberas in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-25-2013, 03:53 PM
  3. Replies: 5
    Last Post: 04-20-2012, 08:54 AM
  4. formula logic (sumproduct,countif)
    By TROBFP in forum Excel General
    Replies: 1
    Last Post: 04-22-2007, 08:48 PM
  5. Replies: 3
    Last Post: 03-28-2006, 03:56 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