+ Reply to Thread
Results 1 to 4 of 4

Sumproduct - ignore blank cells

  1. #1
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Sumproduct - ignore blank cells

    Hi,

    I have a sumproduct formula which I need help with if possible...

    The formula:
    =SUMPRODUCT((Budget!$A$3:$A$16=$B$3)*(Budget!$B$3:$B$16=$C$4)*(Budget!$C$2:$D$2=$B6)*(Budget!$C$3:$D$16))

    Works providing there is no blank cells on the budget sheet column C however due to my data is exported this column will always be blank.

    Is there any way in which I can amend the formula to ignore blank cells. I know I can change the array but I do not want to do this as in my data I have other instances where I have blank cells.

    Regards

    Paul
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Sumproduct - ignore blank cells

    I guess you can do something like:
    =SUM((Budget!$A$3:$A$16=$B$3)*(Budget!$B$3:$B$16=$C$4)*(Budget!$C$2:$D$2=$B6)*IF(Budget!$C$3:$D$16="",0,Budget!$C$3:$D$16))
    as an array formula (confirm with ctrl+shift+enter)
    ?

    edit, thinking about it, you just need:
    =SUMPRODUCT((Budget!$A$3:$A$16=$B$3)*(Budget!$B$3:$B$16=$C$4)*(Budget!$C$2:$D$2=$B6),(Budget!$C$3:$D$16))

  3. #3
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: Sumproduct - ignore blank cells

    That worked - just a comma! Why?

    Thank you by the way!

  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Sumproduct - ignore blank cells

    using arithmetic like =A1*B1 creates an error with blank cells, the sum() and sumproduct() functions treat blank cells as 0's.
    so your sumproduct function was actually just summing a single array because you multiplied all the arrays together with the brackets. adding the comma creates two different arrays that are combined using the sumproduct function which copes with the blanks.

+ 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. Ignore Blank Cells in SUMPRODUCT formula
    By taniwha in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-22-2013, 10:52 AM
  2. How can I make a macro ignore blank cells? - Blank Cells have formulas
    By mz1161 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-30-2013, 08:54 AM
  3. Sumproduct: how to ignore blank variables
    By robotlust in forum Excel General
    Replies: 5
    Last Post: 05-17-2012, 11:02 PM
  4. [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
  5. Replies: 2
    Last Post: 04-28-2012, 05:13 AM

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