+ Reply to Thread
Results 1 to 3 of 3

SUMIFS Problem

  1. #1
    Registered User
    Join Date
    12-01-2008
    Location
    London
    Posts
    25

    SUMIFS Problem

    Hello Excel Forum.
    Would really appreciate some help.

    My SUMIFS has 4 criteria. 3 of which are in drop-down lists.

    The spreadsheet works perfectly when Status2 is set to any value (which is what I desire)

    But as soon as I set the next criteria, Status1 to any value in the drop-down, then my spreadsheet goes to all zeros. But the values will pop up if I set the next criteria,Loss Threshold.

    I would like the spreadsheet to show the values when I have Status1 and Status2 criteria set.

    Summary:
    Just Status 1 set: perfect
    Status1/Status2/Loss Threshold set: perfect
    Status1 and Status2 set: no data shows - this is the problem

    (Note: I can get the right result if I use sumproduct instead of SUMIFS, but I want to use SUMIFS for this spreadsheet)

    Many thanks Forum - Hope this is an interesting case to look at and learn from
    denise
    Attached Files Attached Files
    Last edited by denise001; 10-12-2011 at 08:41 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: SUMIFS Problem

    Try revising formula a bit to:

    Please Login or Register  to view this content.
    in C11, copied across the matrix.

    The formula now coerces blank B6 to 0 by adding +0 and it coerces blanks into "all" by adding * wildcard to the B2 and B4 criteria.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    12-01-2008
    Location
    London
    Posts
    25

    Re: SUMIFS Problem

    Hello NBVC.
    Always so reliable. Really really appreciate the quick reply. I would have never thought of your solution.

    It works!

    The IF part of the formula becomes redundant - so removing this and using your formula now all the combinations that I desire in the dropdown work and produces data instead of zeros.

    Here is the formula:

    PHP Code: 
    =SUMIFS('Raw Data'!D$2:D$235,'Raw Data'!$T$2:$T$235,">="&$B$6+0,'Raw Data'!$C$2:$C$235,$B$2&"*",'Raw Data'!$B$2:$B$235,$B$4&"*",'Raw Data'!$A$2:$A$235,$B11
    Revised spreadsheet re-uploaded for those who want to see it in action.

    Thank you NBVC - What a guy! and Roy UK too!
    denise
    Attached Files Attached Files
    Last edited by denise001; 10-12-2011 at 09:07 AM. Reason: Add extra thanks!

+ 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