Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: SUMIFS function has suddenly stopped working in one cell
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2016
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SUMIFS function has suddenly stopped working in one cell

    Please help if you can...

    I setup a spreadsheet for reports last year that contains quite a few SUMIFS calculations.

    I have several tabs on the spreadsheet that each have these calculations and the majority all work fine.

    However, in every tab I have one of these SUMIFS calculations in cell L27. This calculation is now returning no results at all.

    The formula I'm using is =SUMIFS(G:G,D:D,"Mural")

    Any thoughts on how to fix will be greatly appreciated.

  2. #2
    New Member
    Join Date
    Oct 2016
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIFS function has suddenly stopped working in one cell

    SUMIFS requires more arguments. It looks like, from your formula above, that you need SUMIF instead and with the criteria "Mural" in the middle.

  3. #3
    New Member
    Join Date
    Nov 2016
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIFS function has suddenly stopped working in one cell

    Quote Originally Posted by KenDue View Post
    SUMIFS requires more arguments. It looks like, from your formula above, that you need SUMIF instead and with the criteria "Mural" in the middle.
    Thanks Ken.

    Could you provide an example formula. I tried a SUMIF formula and the workbook wouldn't let me save it. The SUMIFS formula I've used has worked in all other cells, but I'm happy to try something else - sorry if this is a bit basic...

    Tom

  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 function has suddenly stopped working in one cell

    What do you get with the following?

    =SUM(G:G)

    >> ?

    =COUNTIFS(D:D,"Mural")

    >> ?
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    New Member
    Join Date
    Oct 2016
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIFS function has suddenly stopped working in one cell

    SUMIF takes the format of range, criteria, sum-range. If your numbers to sum reside in column G, your formula would be =SUMIF(D:D,"Mural",G:G).
    Last edited by KenDue; Nov 24th, 2016 at 10:38 AM.

  6. #6
    New Member
    Join Date
    Nov 2016
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIFS function has suddenly stopped working in one cell

    Quote Originally Posted by Aladin Akyurek View Post
    What do you get with the following?

    =SUM(G:G)

    >> 7898.94

    =COUNTIFS(D:D,"Mural")

    >> 0
    I don't seem to get a result for COUNTIFS or COUNTIF for "mural" but I do for other items in the column "print" for example.

  7. #7
    New Member
    Join Date
    Nov 2016
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIFS function has suddenly stopped working in one cell

    Thanks Ken. I've tried this formula too and unfortunately still get a 0 result. despite me knowing that the COUNTIFS on the D:D column should be 9.

  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 function has suddenly stopped working in one cell

    Quote Originally Posted by tomplum View Post
    I don't seem to get a result for COUNTIFS or COUNTIF for "mural" but I do for other items in the column "print" for example.
    Does the following work?...

    =SUMIFS(G:G,D:D,"*Mural*")
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    New Member
    Join Date
    Oct 2016
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIFS function has suddenly stopped working in one cell

    I believe Aladin is onto the right idea with using the wildcard character "*Mural*" in the formula, but the formula still needs to be
    =SUMIF(D:D,"*Mural*",G:G) (with the criteria in the center of the column designations). I've tested this and it works perfectly, even if the word "mural" is all lowercase. I'm guessing your sheet has a leading or trailing space where "mural" is listed that the wildcard character will take care of.
    Last edited by KenDue; Dec 19th, 2016 at 10:40 PM.

  10. #10
    Board Regular
    Join Date
    Dec 2016
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIFS function has suddenly stopped working in one cell

    Hi

    Structure of your sumif formula is wrong

    Sumif(Range, Criteria, SumRange)

    always use "=Text" while not referring a cell

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
  •