+ Reply to Thread
Results 1 to 9 of 9

Seeking SUMIFS, IF and ISBLANK harmony...please help!

  1. #1
    Registered User
    Join Date
    04-28-2016
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    15

    Seeking SUMIFS, IF and ISBLANK harmony...please help!

    Hi!

    I have a spreadsheet which I have created for my team of project managers to report finances of different programs.

    So far it's work great, but I'd like to take it to the next level. I've made a summary table which currently looks at an activities stage in a project, the month the invoice has been raised, and the amount being charged.

    This is great for seeing what has actually happened, but I would like it to also show what is planned. So I tried to write a formula that, should the 'Invoice Month' cell be blank, it looks at the 'Planned Delivery Month' cell. However, I'm still getting a zero returned to me. I have pasted my original and updated formula below, but I suspect they are too complicated.

    Original
    =SUMIFS(Main_Input[Gross Charge],Main_Input[Invoice Month],[@Month],Main_Input[CBS Phase],Activity_by_Value[[#Headers],[Connect]])

    Updated
    =SUMIFS(Main_Input[Gross Charge],IF(ISBLANK(Main_Input[Invoice Month]),Main_Input[Planned Delivery Month],Main_Input[Invoice Month]),[@Month],Main_Input[CBS Phase],Activity_by_Value[[#Headers],[Connect]])

    I should point out as well that my Invoice Month cell has a formula in it which may be causing the problem...

    =IF(ISBLANK([@[Date to be invoiced]])," ",TEXT([@[Date to be invoiced]],"mmm-yy"))

    Please help!!!

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Seeking SUMIFS, IF and ISBLANK harmony...please help!

    You will need to post a sample file (not image) showing expected results.

    To upload file click "Go Advanced" then "Manage Attachments"

  3. #3
    Registered User
    Join Date
    04-28-2016
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    15

    Re: Seeking SUMIFS, IF and ISBLANK harmony...please help!

    I don't think I can as it has sensitive data and company names all over it.

    Thanks, I'll keep trying and post in here if I find a solution!

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Seeking SUMIFS, IF and ISBLANK harmony...please help!

    Remove the sensitive data for a file with 20 or so rows.

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Seeking SUMIFS, IF and ISBLANK harmony...please help!

    Quote Originally Posted by TommyG85 View Post

    Original
    =SUMIFS(Main_Input[Gross Charge],Main_Input[Invoice Month],[@Month],Main_Input[CBS Phase],Activity_by_Value[[#Headers],[Connect]])

    Updated
    =SUMIFS(Main_Input[Gross Charge],IF(ISBLANK(Main_Input[Invoice Month]),Main_Input[Planned Delivery Month],Main_Input[Invoice Month]),[@Month],Main_Input[CBS Phase],Activity_by_Value[[#Headers],[Connect]])
    I absolutely loathe formulas written in structured referencing.

    There is no way anyone can interpret what that's doing without seeing the file.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Seeking SUMIFS, IF and ISBLANK harmony...please help!

    The ISBLANK test on a range isn't accepted in the SUMIF(S) function.

    You'll probably need to use a different function like SUM/IF array or SUMPRODUCT.

  7. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Seeking SUMIFS, IF and ISBLANK harmony...please help!

    Add a helper column to the Main_Input table, call it say 'Helper'
    In that column put
    =IF(Main_Input[@Invoice Month]="",Main_Input[@Planned Delivery Month],Main_Input[@Invoice Month])

    Then use
    =SUMIFS(Main_Input[Gross Charge],Main_Input[Helper],[@Month],Main_Input[CBS Phase],Activity_by_Value[[#Headers],[Connect]])



    Also, you said you have this in the Invoice Month field
    =IF(ISBLANK([@[Date to be invoiced]])," ",TEXT([@[Date to be invoiced]],"mmm-yy"))

    You should change that to
    =IF(ISBLANK([@[Date to be invoiced]]),"",TEXT([@[Date to be invoiced]],"mmm-yy"))

    " " is not an empty string, it is a SPACE character.
    change " " to ""

  8. #8
    Registered User
    Join Date
    04-28-2016
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    15

    Re: Seeking SUMIFS, IF and ISBLANK harmony...please help!

    Now I can't attach the file! I've converted it to a range and pasted the orginal formula below. I've managed to insert a screenshot too.

    Original Formula
    =SUMIFS($J$18:$J$27,$A$18:$A$27,B$1,$O$18:$O$27,$A2)

    My attempted Formula


    =SUMIFS($J$18:$J$27,IF(ISBLANK($O$18:$O$27),$M$18:$M$27,$O$18:$O$28),B$1,$O$18:$O$27,$A2)

    Excel Help.png

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Seeking SUMIFS, IF and ISBLANK harmony...please help!

    I can't see *.png images on this site.

    Quote Originally Posted by TommyG85 View Post

    =SUMIFS($J$18:$J$27,IF(ISBLANK($O$18:$O$27),$M$18:$M$27,$O$18:$O$28),B$1,$O$18:$O$27,$A2)
    The logic of that formula looks a bit strange.

    If the range O18:O27 is blank use the range M18:M27 and test that it equals cell B1.

    Then, a separate test for the same range O18:O27 that equals cell A2.

    Those conditions contradict one another.

    Unless B1 and A2 are the same O18:O27 can not be both equal to B1 and equal to A2 at the same time.

    Can you make up a SMALL sample file and show us what result you expect?

    20 rows and a few columns worth of data is all we need. We don't need any irrelevant data. It just makes it harder to find what we're looking for!

+ 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. [SOLVED] IF formula not working =IF(NOT(ISBLANK(M3)),"",IF(ISBLANK(L3),"",TODAY()-L3))
    By amthyst826 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2014, 02:37 PM
  2. ISBLANK with SUMIFS?
    By Cunner in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-25-2013, 09:47 AM
  3. SUMIFS Puzzle - Trying to avoid adding multiple SUMIFS to get valid result
    By haldavid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-09-2013, 03:42 PM
  4. [SOLVED] Sumifs, problem with the formula: =sumifs(c10:c200,<=today(),0)
    By Faustocruz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-08-2012, 04:26 AM
  5. Help with ISBLANK function referring to a cell with another ISBLANK formula
    By camdameron in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-23-2011, 12:45 PM
  6. Excel 2007 : How to make two VB codes work in harmony?
    By incisivekeith in forum Excel General
    Replies: 0
    Last Post: 11-22-2009, 09:45 PM
  7. Seeking a formula...
    By lesham in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-08-2008, 08:53 AM

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