+ Reply to Thread
Results 1 to 2 of 2

SUMPRODUCT with AND problem

  1. #1
    Registered User
    Join Date
    09-21-2013
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    39

    SUMPRODUCT with AND problem

    Hi,

    Please can you help me understand why this formula isn't working:

    =IF(F675<>"Ticket","",SUMPRODUCT(($O$13:O675=O675)*($F$13:$F675="Ticket")*AND($E$13:$E675<>"Completed",$E$13:$E675<>"Cancelled")))

    If Column E contains anything other than completed and cancelled I want the formula to count but it isn't. If I take out the AND at the end and just count <> completed it works perfectly...

    =IF(F675<>"Ticket","",SUMPRODUCT(($O$13:O675=O675)*($F$13:$F675="Ticket")*($E$13:$E675<>"Completed")))

    Thanks

    Andy

  2. #2
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: SUMPRODUCT with AND problem

    AND will not calculate correctly in array formula. You may simply multiply additional criteria:
    =IF(F675<>"Ticket","",SUMPRODUCT(($O$13:O675=O675)*($F$13:$F675="Ticket")*($E$13:$E675<>"Completed")*($E$13:$E675<>"Cancelled")))
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

+ 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. problem sumproduct ?
    By vumian in forum Excel General
    Replies: 8
    Last Post: 02-18-2008, 11:54 AM
  2. [SOLVED] Sumproduct Problem
    By Andibevan in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  3. Another Sumproduct & #N/A problem
    By Dave Davis in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-10-2005, 12:06 PM

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