+ Reply to Thread
Results 1 to 16 of 16

SUMIFS not working

  1. 05-25-2022, 02:51 PM #1
    vani2004
    vani2004 is offline
    Registered User
    Join Date
    03-18-2014
    Location
    burlington, nj
    MS-Off Ver
    Excel 365
    Posts
    71

    Question SUMIFS not working

    I am not sure why my SUMIFS formula not working. I have used Sumifs for several years now and never had this issue. I have attached the file as well.

    Column A

    Acct (A1)
    520100 (A2)
    520010 (A3)

    Column B in cell B2 i have sumif formula as follows
    =sumifs(E:E,D:D,A2,D:D,A3) - I am getting answer Zero
    I also tried Sumifs(E2:E7,D2:D7,A2,D2:D7,A3)

    Column D is a list of all account numbers
    Column E shows the amount for each account numbers

    I made sure accounts in Cell A2 & A3 are present in column D and there is an associated amount in Column E.

    This should be very simple! I am not sure why it's not working!!!!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 05-25-2022, 02:58 PM #2
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: SUMIFS not working

    B2=sumifs(e:e,d:d,a2)

    Or use pivot table.

    See the attached file.
    Attached Files Attached Files
    Last edited by oeldere; 05-25-2022 at 03:03 PM. Reason: added file with pivot table
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.
    Reply With QuoteRegister To Reply
  3. 05-25-2022, 02:58 PM #3
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,080

    Re: SUMIFS not working

    Are you trying to sum col E for both A2 & A3, or trying to get the individual sum for each?
    Reply With QuoteRegister To Reply
  4. 05-25-2022, 03:02 PM #4
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: SUMIFS not working

    You're never going to get both conditions to be true on any one line.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  5. 05-25-2022, 03:04 PM #5
    vani2004
    vani2004 is offline
    Registered User
    Join Date
    03-18-2014
    Location
    burlington, nj
    MS-Off Ver
    Excel 365
    Posts
    71

    Re: SUMIFS not working

    i am trying to sum col E for both A2 & A3
    Reply With QuoteRegister To Reply
  6. 05-25-2022, 03:05 PM #6
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,080

    Re: SUMIFS not working

    In that case try
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 05-25-2022, 03:23 PM #7
    vani2004
    vani2004 is offline
    Registered User
    Join Date
    03-18-2014
    Location
    burlington, nj
    MS-Off Ver
    Excel 365
    Posts
    71

    Re: SUMIFS not working

    Quote Originally Posted by Fluff13 View Post
    In that case try
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    It #SPILL - as if I am individually summing up for each (summing up for each account only)
    Reply With QuoteRegister To Reply
  8. 05-25-2022, 03:25 PM #8
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,080

    Re: SUMIFS not working

    That formula will not give a #spill error, so you must have entered it wrongly. Also your profile shows you are using xl2016, which is obviously wrong as Spill ranges didn't exist.
    Reply With QuoteRegister To Reply
  9. 05-25-2022, 03:26 PM #9
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: SUMIFS not working

    Works for me. How and where are you entering the formula?
    Reply With QuoteRegister To Reply
  10. 05-25-2022, 03:27 PM #10
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,080

    Re: SUMIFS not working

    If you want an individual sum (which is not what you said before), then use
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    in B2 only & make sure B3 is empty
    Reply With QuoteRegister To Reply
  11. 05-25-2022, 03:28 PM #11
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: SUMIFS not working

    I suspect you have missed the SUM part.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    will give a #SPILL! error if there is anything below it.
    Last edited by TMS; 05-25-2022 at 03:30 PM.
    Reply With QuoteRegister To Reply
  12. 05-25-2022, 03:32 PM #12
    vani2004
    vani2004 is offline
    Registered User
    Join Date
    03-18-2014
    Location
    burlington, nj
    MS-Off Ver
    Excel 365
    Posts
    71

    Re: SUMIFS not working

    Quote Originally Posted by Fluff13 View Post
    That formula will not give a #spill error, so you must have entered it wrongly. Also your profile shows you are using xl2016, which is obviously wrong as Spill ranges didn't exist.
    Yes, I did enter wrong - I missed the SUM in front of Sumifs. Your suggestion is working Thank you so much for your help.
    Reply With QuoteRegister To Reply
  13. 05-25-2022, 03:34 PM #13
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: SUMIFS not working

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
    Reply With QuoteRegister To Reply
  14. 05-25-2022, 03:34 PM #14
    vani2004
    vani2004 is offline
    Registered User
    Join Date
    03-18-2014
    Location
    burlington, nj
    MS-Off Ver
    Excel 365
    Posts
    71

    Re: SUMIFS not working

    Quote Originally Posted by TMS View Post
    I suspect you have missed the SUM part.
    You're correct - I did miss SUM part. Thank you
    Reply With QuoteRegister To Reply
  15. 05-25-2022, 03:34 PM #15
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,080

    Re: SUMIFS not working

    Glad to help & thanks for the feedback.
    Reply With QuoteRegister To Reply
  16. 05-25-2022, 04:12 PM #16
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: SUMIFS not working

    You're welcome. Thanks for the rep.


    Thanks also for updating your profile.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] sumifs not working, but same formula working else where
    By DEEARO in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-02-2021, 05:47 AM
  2. [SOLVED] sumifs not working
    By jimmiboy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-28-2021, 12:28 AM
  3. multiple criteria in sumifs - sum(sumifs({}...)) not working without ctrl+shift+enter
    By bgru in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-22-2017, 03:49 PM
  4. Working with a Sumifs
    By yuenk in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-15-2017, 02:17 AM
  5. Sumifs not working
    By Rob K in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-26-2015, 03:05 PM
  6. SUMIFS not working
    By Luther.King in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-03-2014, 08:02 PM
  7. [SOLVED] SUMIFS - Not Working as I would like
    By Chris.Williams81 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-08-2013, 08:55 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1