+ Reply to Thread
Results 1 to 7 of 7

SUMIFS Formula not giving desired result

  1. #1
    Registered User
    Join Date
    10-12-2013
    Location
    Perth, Western Australia
    MS-Off Ver
    Excel 2013
    Posts
    3

    Cool SUMIFS Formula not giving desired result

    Hi,

    I am a using Excel 2013 and I am having trouble understanding why a formula I am working with isn't working.

    The problem: So I am creating an Excel Worksheet that gets income and expense data from a MySQL database, imports it into tables and then formulas determine expenses and income for a given year. I have linked up MySQL and that's all working fine but my problem is with the expenses calculation.

    The formula requires data from two sheets:
    • Annual Expenses 2013: contains the calculations based on the Expenses sheet. It creates a table based on the categories made ([Expense Name]) in the MySQL database and then calculates from the Expenses sheet what expenses were made (expenses_totalsum) in that category given they were made in the a year specified in a cell (annual_expense_year).
    • Expenses: contains the data imported from the MySQL database. The fields I am working with in this sheet are the category the expense is filed in (expenses_category_name) and the year the expense was made (expense_year).

    So the formula I used in the first place was: =SUMIF(expenses_category_name,[Expense Name],expenses_totalsum). This would search through the category name in the Expenses sheet, match it with the Expense Name in the Annual Expenses 2013 sheet and return the SUM of said values that matched those criteria from the expenses_totalsum column in the Expenses sheet.

    This all worked fine but now I need to introduce the year factor. So I need to achieve the same thing except any value it finds must also search the Expenses sheet for the year the expense was made and match it to the year set in the Annual Expenses 2013 Sheet.

    This is the formula I have tried amongst other things but I can see where I have gone wrong: =SUMIFS(expenses_totalsum,expenses_category_name,[Expense Name],expenses_year,annual_expense_year) this just returns a value of 0.
    Just a little extra info: the annual_expense_year value is a cell with the year in it within the Annual Expenses 2013 sheet. The expense_year is a column of data within the Expenses sheet (each expense is assigned a date and this column just displays the year of that date).

    I can upload the worksheet if anyone want's it I just don't know if the data will also move with the sheet given the MySQL is on a localhost (for now).

    Thanks for any help and I hope I have explain it well. I tried my best but it's a little complex.
    Last edited by lukemelville; 10-12-2013 at 07:58 AM. Reason: Added attachment

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,081

    Re: SUMIFS Formula not giving desired result

    Hi and welcome to the forum

    the 1st thing I would check would be to make sure that the dates are in the same format on both sides. You may find that what looks like just a year number (2013), may in fact be a full date, just formatted to show just the year - it may look like 10/12/2013 for instance if formatted differently.

    On the other hand, 1 of the date "sets" may be just that, a 4-digit number.

    Its really hard to say for sure what the problem is without seeing a sample of what you are working with To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,221

    Re: SUMIFS Formula not giving desired result

    If the expense_year column contains date values but only displays the year then they aren't going to match the year in the annual_expense_year cell.

    There are probably a few ways to handle it, for example use criteria to find dates in the expense year that are between the start and end of the year in annual_expense_year.

    Can you upload an example workbook?

    Click on GO ADVANCED and use the paperclip icon to open the upload window.
    If posting code please use code tags, see here.

  4. #4
    Registered User
    Join Date
    10-12-2013
    Location
    Perth, Western Australia
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: SUMIFS Formula not giving desired result

    Thanks for the responses @FDibbins and @Norie. I think you both are onto something with the expense_year value.
    I uploaded and example for you to look at. Don't judge for the way it's laid out so far. I am getting the formulas and that right before I make it look good and flow well

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,221

    Re: SUMIFS Formula not giving desired result

    Try this.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    By the way, there's a couple of things that might make things a bit easier.

    For example, you could use the MySQL query to return the year with the YEAR function, eg YEAR([created])

    Also, the data you have is in tables so you could use structured table referencing rather than named ranges.

    With structured table referencing you could use something like this
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Note, I changed the table name to ExpTable - Table_Query_from_TechCloudEnt__LocalDatabase3 was a bit of a handful.

  6. #6
    Registered User
    Join Date
    10-12-2013
    Location
    Perth, Western Australia
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: SUMIFS Formula not giving desired result

    Thanks for your help @Norie. That formula is perfect. I have noted your suggestion to use structured table referencing. I am just playing around with Excel at the moment. Working on a bigger idea that might work something like this.
    Have a good evening.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,081

    Re: SUMIFS Formula not giving desired result

    Happy to help and thanks for the feedback

+ 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. Formula giving different result on two different pcs
    By mdooley in forum Excel General
    Replies: 2
    Last Post: 05-28-2012, 07:05 PM
  2. macro not giving desired output
    By coolanks1 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-23-2009, 07:36 AM
  3. Struggling With How to Create A Formula for Desired Result...
    By oxyron in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-01-2007, 03:36 PM
  4. Sumif formula not giving result
    By abanerji in forum Excel General
    Replies: 5
    Last Post: 06-13-2006, 02:25 PM
  5. Formula not giving me the right result
    By Steved in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-09-2006, 10:25 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