+ Reply to Thread
Results 1 to 3 of 3

SumProduct function

  1. #1
    Registered User
    Join Date
    01-31-2011
    Location
    San Jose, CA
    MS-Off Ver
    Excel 2007
    Posts
    6

    SumProduct function

    Hey there,

    I am working on a large sumproduct function that is returning an incorrect total. What I am essentially trying to do is get a count of the number of rows that contain certain criteria. Currently my function looks like this:

    =SUMPRODUCT((Accounts.Old!$CE$2:$CE$50000<>"Prospect")*(Accounts.Old!$CE$2:$CE$50000<>"Inactive")*(Accounts.Old!$BQ$2:$BQ$50000<>" ")*(Accounts.Old!$BQ$2:$BQ$50000<>"Placeholder")*(Accounts.Old!$F$2:$F$50000<>"CSP - Exc I AND C")*(Accounts.Old!$F$2:$F$50000<>"CSP - Inc I AND C")*(Accounts.Old!$F$2:$F$50000<>"I & C")*(Accounts.Old!$F$2:$F$50000<>"I AND C")*(Accounts.Old!$F$2:$F$50000<>"Resell HDS Services")*(Accounts.Old!$F$2:$F$50000<>" ")*1)

    It should be returning 160, but instead is returning 693. Is there something I am doing wrong here?

    2 side things, if you see an easier way of writing the function I am all ears. Also I am doing a lot of "<>" because I couldn't get an OR type function to work.

    If you have any questions I will answer as fast as possible.

    Thanks.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,631

    Re: SumProduct function

    Please post a sample workbook ... or the real thing if the data is not sensitive or personal.

    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: SumProduct function

    In general if you want to check that a range isn't blank you would use "" to indicate a blank, " " indicates a space so that isn't correct unless you actually have single spaces in some of those ranges.

    Also you can use an ISNA(MATCH construction for "multiple exclusions" so perhaps try like this

    =SUMPRODUCT(ISNA(MATCH(Accounts.Old!$CE$2:$CE$50000,{"Prospect","Inactive"},0))+0, (Accounts.Old!$BQ$2:$BQ$50000<>"")+0,(Accounts.Old!$BQ$2:$BQ$50000<>"Placeholder")+0,ISNA(MATCH(Accounts.Old!$F$2:$F$50000,{"CSP - Exc I AND C","CSP - Inc I AND C","I & C","I AND C","Resell HDS Services"},0))+0,(Accounts.Old!$F$2:$F$50000<>"")+0)

    or COUNTIFS might be a little quicker in Excel 2007 but the syntax will be a little different, you'd have to list out each range again.....
    Audere est facere

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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