+ Reply to Thread
Results 1 to 7 of 7

SUMPRODUCT where data and criteria ranges have blank values

  1. #1
    Registered User
    Join Date
    07-06-2016
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    46

    SUMPRODUCT where data and criteria ranges have blank values

    Hi guys,

    This is probably a simple one.

    I always seem to have trouble with sumproduct when there is blank cells in the sum range or criteria range (or formulas returning no value).

    I want to return the sum of a data table $V$4:$Z$11 with criteria range $Q$4:$Q$11 and $O$4:$O$11 - see my attempt at the sumproduct from B4:M4 - Please solve for these cells

    As this table is filled in over time the sum range will often have blank cells (by way of a formula) and the criteria ranges will also have blank cells (by way of formula and input).

    I think this is solved with -- but not sure of how to use this and what it actually does.

    Result for B4 should equal $0, while C4 should equal $3,000

    Thanks in advance

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: SUMPRODUCT where data and criteria ranges have blank values

    Try this ...

    =SUM(INDEX($V$4:$Z$11,MATCH(1,INDEX(($Q$4:$Q$11=$A$2)*($O$4:$O$11=B$1),0),0),0))

  3. #3
    Registered User
    Join Date
    07-06-2016
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    46

    Re: SUMPRODUCT where data and criteria ranges have blank values

    Hi Phuocam, it only sums the first number that it meets the criteria. There will be more than i row for each month. Sorry i should have noted this

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: SUMPRODUCT where data and criteria ranges have blank values

    For insurance try this change in O4:O11. The blanks were returning 1s. This will return 0s.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then change the formula B4:M4 to this
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The N function converts strings and blank cells to zeros. That was the problem. However N is resistant to accepting whole ranges. The range has to be coerced into an array. That is what the + is about.


    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    2
    Fork Lagoon
    Jan-18
    Feb-18
    Mar-18
    Apr-18
    May-18
    Jun-18
    Jul-18
    Aug-18
    Sep-18
    Oct-18
    Nov-18
    Dec-18
    3
    4
    Revenues [Internal]
    0
    3000
    9000
    0
    0
    0
    0
    0
    0
    0
    0
    0
    5
    Dave

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: SUMPRODUCT where data and criteria ranges have blank values

    I think this is solved with -- but not sure of how to use this and what it actually does.
    That is referred to as the double unary. Any math operation performed upon logical tests coerces TRUE/FALSE into their underlying numeric values 1/0. "--" is only one such method. Others you may see are +0 or *1. The both perform the same coercion.

  6. #6
    Registered User
    Join Date
    07-06-2016
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    46

    Re: SUMPRODUCT where data and criteria ranges have blank values

    Thanks you again

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: SUMPRODUCT where data and criteria ranges have blank values

    You're welcome. Once again thanks for the feedback and marking your thread Solved.

+ 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. SUMPRODUCT, multiple criteria including ranges that are listed horozontally
    By Manwithaplan in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-05-2016, 12:51 AM
  2. [SOLVED] Sumproduct with month, year, other criteria, and blank
    By bibbi2 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-29-2015, 12:47 AM
  3. Replies: 5
    Last Post: 03-31-2014, 12:56 PM
  4. SUMIFS or SUMPRODUCT with multiple criteria including date ranges
    By baxcat in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-25-2013, 09:40 AM
  5. [SOLVED] How to get Sumproduct to ignore a criteria if it is blank?
    By skysurfer in forum Excel General
    Replies: 3
    Last Post: 04-29-2012, 04:54 PM
  6. Replies: 2
    Last Post: 04-28-2012, 05:13 AM
  7. sumproduct for blank values
    By excelnerd1 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-03-2009, 10:41 PM

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