Results 1 to 9 of 9

Thread: SUMIFS, if 0 then = sheet1!C2
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SUMIFS, if 0 then = sheet1!C2

    Hey all,

    I need excel to try to find data meeting 2 criteria and if the data does not meet them, to default to the amount in sheet1!C2

    I currently have this working perfectly:
    =SUMIFS(Journal!$B$4:$B$1000,Journal!$A$4:$A$1000,Cashflow!B8,Journal!$C$4:$C$1000,">="&Cashflow!C$6-1,Journal!$C$4:$C$1000,"<="&Cashflow!D$6)

    Which says: sumrange (Journal!b4:b1000), if A is found in CriteriaRange1, and B is above (a date)-1 but below (another date) then return the corresponding figure in the sumrange. But it returns a value of "0" if criteria not met - as i said, I need it to default to the value provided in sheet1!C2

    Thank you!
    SPTE

  2. #2
    Board Regular
    Join Date
    Oct 2002
    Location
    Sydney, Australia
    Posts
    790
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIFS, if 0 then = sheet1!C2

    Hi SPTE

    Does this do what you want:

    Code:
    =IF(SUMIFS(Journal!$B$4:$B$1000,Journal!$A$4:$A$1000,Cashflow!B8,Journal!$C$4:$C$1000,">="&Cashflow!C$6-1,Journal!$C$4:$C$1000,"<="&Cashflow!D$6)=0,
    Sheet1!C2,
    SUMIFS(Journal!$B$4:$B$1000,Journal!$A$4:$A$1000,Cashflow!B8,Journal!$C$4:$C$1000,">="&Cashflow!C$6-1,Journal!$C$4:$C$1000,"<="&Cashflow!D$6))
    Cheers

    pvr928
    Work: Excel 2010 64 bit
    Home: Excel 2016 64 bit

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: SUMIFS, if 0 then = sheet1!C2

    Something like

    =IF(COUNTIFS(Journal!$A$4:$A$1000,Cashflow!B8,Journal!$C$4:$C$1000,">="&Cashflow!C$6-1,Journal!$C$4:$C$1000,"<="&Cashflow!D$6),
    SUMIFS(Journal!$B$4:$B$1000,Journal!$A$4:$A$1000,Cashflow!B8,Journal!$C$4:$C$1000,">="&Cashflow!C$6-1,Journal!$C$4:$C$1000,"<="&Cashflow!D$6),S
    heet1!C2)
    Assuming too much and qualifying too much are two faces of the same problem.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: SUMIFS, if 0 then = sheet1!C2

    If Microsoft would ever come to terms with SETV/GETV (Longre), we could have:

    =IF(SETV(SUMIFS(Journal!$B$4:$B$1000,Journal!$A$4:$A$1000,Cashflow!B8,Journal!$C$4:$C$1000,">="&Cashflow!C$6-1,Journal!$C$4:$C$1000,"<="&Cashflow!D$6)),GETV(),Sheet1!C2)
    Last edited by Aladin Akyurek; Jan 24th, 2018 at 01:26 AM.
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    New Member
    Join Date
    Jan 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIFS, if 0 then = sheet1!C2

    Quote Originally Posted by pvr928 View Post
    Hi SPTE

    Does this do what you want:

    Code:
    =IF(SUMIFS(Journal!$B$4:$B$1000,Journal!$A$4:$A$1000,Cashflow!B8,Journal!$C$4:$C$1000,">="&Cashflow!C$6-1,Journal!$C$4:$C$1000,"<="&Cashflow!D$6)=0,
    Sheet1!C2,
    SUMIFS(Journal!$B$4:$B$1000,Journal!$A$4:$A$1000,Cashflow!B8,Journal!$C$4:$C$1000,">="&Cashflow!C$6-1,Journal!$C$4:$C$1000,"<="&Cashflow!D$6))
    Cheers

    pvr928
    ________________________________________________________________________________________________________________________ ______________________

    Ahh well done! Because the issue occurred after i had input the formula, I predisposed my brain to thinking the solution would have to go immediatly after the existing formula e.g. =sumif(etc.etc)OR(etc.etc). But your solution was to consider the issue first hence the IF.... Thank you very much! Interesting physiology behind it too


    SPTE

  6. #6
    New Member
    Join Date
    Jan 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIFS, if 0 then = sheet1!C2

    I have no idea what SETV and GETV but interested if you care to explain =)

    SETV

  7. #7
    New Member
    Join Date
    Jan 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIFS, if 0 then = sheet1!C2

    Again thank you for your help. Now it keeps asking me to update my values in a pop up window... any ideas?

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: SUMIFS, if 0 then = sheet1!C2

    Quote Originally Posted by SkatePropertyTofuEquities View Post
    Again thank you for your help. Now it keeps asking me to update my values in a pop up window... any ideas?
    Post #3 uses COUNTIFS than SUMIFS if appropriate, otherwise returns Sheet1!C2.

    Post #4 is a hint for Microsoft, not for you.
    Last edited by Aladin Akyurek; Jan 24th, 2018 at 05:14 AM.
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    Board Regular
    Join Date
    Oct 2002
    Location
    Sydney, Australia
    Posts
    790
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIFS, if 0 then = sheet1!C2

    That window appears because it cannot find one or more of the worksheet references in the formula in the current workbook - ie Excel is asking you to point to the workbook which does contain the missing worksheet/s.

    Consequently, check that:

    Journal
    Cashflow
    Sheet1

    all appear in the current workbook (I assume that they are meant to, and that they are not intended to instead exist in a separate workbook).

    Cheers

    pvr928
    Work: Excel 2010 64 bit
    Home: Excel 2016 64 bit

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •