+ Reply to Thread
Results 1 to 4 of 4

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
    326

    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 daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

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

    Try this version

    =SUMPRODUCT((Sheet1!$A$43:$A$211= $A16)*(Sheet1!$B$43:$B$211=$B16)*((Sheet1!$C43:$C$211=$C16)+($C16="")>0)*(Sheet1!$P$43:$R$211))
    Audere est facere

  3. #3
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

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

    Try

    Please Login or Register  to view this content.

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

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

    Thanks guys!

    Lawrence

+ 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