+ Reply to Thread
Results 1 to 7 of 7

sumifs function not working

  1. #1
    Forum Contributor
    Join Date
    08-14-2013
    Location
    LA Baby!!
    MS-Off Ver
    Excel 2007
    Posts
    1,598

    sumifs function not working

    Hello. If I write this formula =SUMIFS(B3:AK3,B1:AK1,"=2013") it works right but if instead of 2013, I refer to a cell that has 2013 in it. It doesn't work. Why is this?

  2. #2
    Valued Forum Contributor
    Join Date
    04-12-2012
    Location
    India
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, 2019, Office 365, Google Spreadhsheet
    Posts
    640

    Re: sumifs function not working

    What are you referring to. Date or Year or text?
    Thanks,
    Anil Dhawan


    Click *Add Reputation to say "Thanks" and don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved) if you are satisfied.

    Don't stop when you are tired. STOP when you are done!

  3. #3
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,865

    Re: sumifs function not working

    Maybe because the Data is having numbers stored as text.. Please attach your sample workbook.. or instead of putting 2013 in the Cell, try putting '2013 to check if that is the problem..
    Cheers!
    Deep Dave

  4. #4
    Valued Forum Contributor
    Join Date
    04-12-2012
    Location
    India
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, 2019, Office 365, Google Spreadhsheet
    Posts
    640

    Re: sumifs function not working

    If a Cell Containing Date, then try the following formula. Assuming you have date in A1 cell -
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If a Cell Containing text, then try the following formula. Assuming you have text in A1 cell -
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    08-14-2013
    Location
    LA Baby!!
    MS-Off Ver
    Excel 2007
    Posts
    1,598

    Re: sumifs function not working

    Spreadsheet attached.
    Attached Files Attached Files

  6. #6
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,865

    Re: sumifs function not working

    Hi,

    Put this in Cell F7

    Please Login or Register  to view this content.
    However, I do not understand 1 thing, the post is about SUMIFS, then why is your sheet showing COUNTIF?

    Secondly when you input a Text, you input it like =SUMIFS(B3:AK3,B1:AK1,"XYZ"), when you input a Number, you input it like =SUMIFS(B3:AK3,B1:AK1,2013) & when you input a cell reference, you input like =SUMIFS(B3:AK3,B1:AK1,I4)

    In you sheet, you input a Cell reference with Double Quote, which makes it a Text String instead of Cell Reference

  7. #7
    Valued Forum Contributor
    Join Date
    04-12-2012
    Location
    India
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, 2019, Office 365, Google Spreadhsheet
    Posts
    640

    Re: sumifs function not working

    Agreed with @NeedForExcel, Your post is stating you are looking for sumifs function however, your sheet is having Countif. Please change the subject of this thread first.

    Second, as said in above post by NeedForExcel, Cell Reference never put in between "" or with "=" sign. You can just keep it like actual reference. If you want any given inputs (in the function only) to work as criteria then you can use both the symbol (its again depend on situation).

    Third, You kept your criteria in B4 and then don't know why you have taken a cell reference to I4. The Countif function work Range (The Range in which you wanted to take the count) and then Criteria (The Criteria which you wanted to match in that given range) is very simple.

    Hope this will make more clear picture.

    Cheers!!!

    Anil

+ 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. SUMIFS not working
    By Luther.King in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-03-2014, 08:02 PM
  2. [SOLVED] SUMIFS not working
    By Krishnab4u in forum Excel General
    Replies: 3
    Last Post: 11-21-2013, 06:31 AM
  3. [SOLVED] Sumifs function not working
    By darkhangelsk in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-16-2013, 03:25 PM
  4. [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
  5. sumifs not working
    By amartino44 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-07-2013, 08:31 PM

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