+ Reply to Thread
Results 1 to 3 of 3

How to get Sumproduct to ignore a criteria if it is blank?

  1. #1
    Forum Contributor
    Join Date
    05-24-2006
    Location
    Los Angeles
    MS-Off Ver
    2019
    Posts
    328

    How to get Sumproduct to ignore a criteria if it is blank?

    Hi,

    I have three criteria for a sumproduct function. One of the criteria is sometimes blank and I would like to then have Sumproduct ignore it and only use the other two criteria.

    I tried inserting an IF function: if the third criteria cell is blank, substitute the range for a "1". But that returns an error.

    =SUMPRODUCT((Sheet1!$A$43:$A$211=$A16)*(Sheet1!$B$43:$B$211=$B16)*(IF(ISBLANK($C16),1,Sheet1!$C43:$C$211=$C16)*(Sheet1!$P$43:$R$211))

    Is this possible in one Sumproduct formula or do I need two Sumproduct formulas?

    Lawrence

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: How to get Sumproduct to ignore a criteria if it is blank?

    Hi,

    you could try an array SUM (confirmed with control+shift+enter)

    Please Login or Register  to view this content.
    but there are for sure better and more elegant solutions.

    Regards
    Last edited by canapone; 04-28-2012 at 01:56 AM.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: How to get Sumproduct to ignore a criteria if it is blank?

    Without knowing the dataset up perhaps:

    Please Login or Register  to view this content.
    or, using a standard OR construct:

    Please Login or Register  to view this content.
    note: with the latter you are open to double counting - best to use a sign based test around ORs (unless explicit requirement to do otherwise), so

    Please Login or Register  to view this content.

+ 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