+ Reply to Thread
Results 1 to 10 of 10

SUMIFS Formula Wrong Result

  1. #1
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    244

    SUMIFS Formula Wrong Result

    Hello,

    This is pretty basic, and although I should be up to the task, for some reason I cannot figure it out.

    In the attached file (tab: "LOBSTER - YEAR" cell: C4), I am using the following formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    That seems to work just fine, but when I use the same formula in cell D4, it gives me the wrong result ($0 vs. $450):
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Any help would be greatly appreciated.

    Thank you.
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: SUMIFS Formula Wrong Result

    There is no data in your file, so it is difficult to see where it is going wrong. Please re-submit with some numbers in, and indicate what you would expect the output values to be.

    Pete

  3. #3
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    244

    Re: SUMIFS Formula Wrong Result

    I entered data for only the cells involved to test the formula. I indicated that the result should be 450 rather than 0.

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: SUMIFS Formula Wrong Result

    It doesn't work because of your use of merged cells, which are an abomination & should be avoided like the plague.
    Your formula is looking at col C for SSR & col D for $$, hence there is no value. Get rid of the merged cells & put SSR into D2 & it will work.

  5. #5
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    244

    Re: SUMIFS Formula Wrong Result

    Thanks so much Fluff13!

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: SUMIFS Formula Wrong Result

    You can use this formula in C4:

    =IF($B4="","",SUMIFS('LOBSTER - MONTH'!C4:$GH4,'LOBSTER - MONTH'!C$2:$GH$2,'LOBSTER - YEAR'!C$2,'LOBSTER - MONTH'!C$3:$GH$3,'LOBSTER - YEAR'!C$3))

    and this one in D4:

    =IF($B4="","",SUMIFS('LOBSTER - MONTH'!D4:GH4,'LOBSTER - MONTH'!C$2:GG$2,C$2,'LOBSTER - MONTH'!D$3:GH$3,D$3))

    Note that instead of using full-column references, I have changed these so that they can be offset by one cell. You can then copy the 2 cells and paste them into the next 2 cells, and so on.

    I would suggest that you use this formula in o4:

    =SUMIFS(C4:M4,C3:M3,O$3)

    which can then be copied into P4, and this will avoid errors caused by explicitly trying to add "" to other "" cells.

    Hope this helps.

    Pete

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: SUMIFS Formula Wrong Result

    Thanks so much Fluff13!
    Glad to help & thanks for the feedback.

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: SUMIFS Formula Wrong Result

    No feedback for me !!

    Pete

  9. #9
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    244

    Re: SUMIFS Formula Wrong Result

    Hi Pete,

    Thank you so much for that suggestion!

    I understand Fluff13's remarks about merged cells, but in trying to make my spreadsheets easier to use/navigate, I do end-up using merged cells. I understand that it complicates things when it comes to formulas and references, but it is extra work I deem acceptable in return for sheet design and end user navigation.

    Given this, your solution is of great help, but I am trying to figure out why yours works and mine didn't.

    Is it simply down to changing the references from full-column references (4:4) to specific references (D4:GH4)? If this is the case, would it be too much to ask to elaborate why the merged cell is problematic for the full-column references and not the other?

    Thanks so much.

  10. #10
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    244

    Re: SUMIFS Formula Wrong Result

    Quote Originally Posted by Pete_UK View Post
    No feedback for me !!

    Pete
    Sorry Pete, was in the middle of studying your response & leaving you feedback when you posted.
    Last edited by Big.Moe; 11-03-2021 at 03:15 PM.

+ 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] Please Tell Me What's Wrong With My SUMIFS Formula
    By hollysq in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-11-2019, 04:31 PM
  2. what's wrong with this SUMIFS formula?
    By pccamara in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-01-2018, 08:29 AM
  3. Displaying wrong result on SUMIFS due to space in left
    By paradise2sr in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-14-2016, 10:00 PM
  4. [SOLVED] Problem with SUMIFS (or am I using the wrong formula???)
    By stubbsj in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-25-2015, 08:42 AM
  5. Formula gives out wrong result, Help Please
    By jyuens13 in forum Excel General
    Replies: 5
    Last Post: 10-28-2014, 07:55 PM
  6. [SOLVED] Code result wrong but formula correct result
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-11-2014, 12:26 PM
  7. SUMIFS Problem - A value used in the formula is of the wrong data type
    By TicklyTigger in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-09-2014, 10:48 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