+ Reply to Thread
Results 1 to 7 of 7

SUMIFS formula returns value 0

  1. #1
    Registered User
    Join Date
    08-07-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    8

    Unhappy SUMIFS formula returns value 0

    Greetings,

    I hope community members and their loved ones are safe and healthy.

    I use the following formula to match and paste values to a different sheet automatically. I am creating automation to help manage my monthly finances.

    Please Login or Register  to view this content.
    I have checked the following (and uploaded a sample sheet with data)
    1. Ensured that the data in columns B, F, and H of the 2023_2024_icici_parth sheet was in the correct format (i.e., dates in column B, text in column F, and numbers in column H).
    2. Check that the values in cells C1 and D1 of the 2023_2023_calculations sheet are valid and within the range of dates in column B of the 2023_2024_icici_parth sheet.
    3. Verify that there are rows in the 2023_2024_icici_parth sheet where the value in column F is ?Salary?, and the value in column H is greater than 0.


    However, I am still getting 0 as the output.

    I used the COUNTIFS to diagnose

    Please Login or Register  to view this content.
    I am still getting 0

    Could I please request assistance from the forum?
    Attached Files Attached Files

  2. #2
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: SUMIFS formula returns value 0

    Make sure the dates in your sheet is a date (numeric) and not a string representing a date.

    If your regional settings for a date correspond to the way in which the dates in columns B and C are currently set,
    you can select a column and click on "text to columns".
    Attached Files Attached Files
    Last edited by HansDouwe; 07-16-2023 at 07:40 AM.

  3. #3
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: SUMIFS formula returns value 0

    You are Welcome.

    Thanks for the rep, inclusive your feedback . Glad to have helped. .

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  4. #4
    Registered User
    Join Date
    08-07-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    8

    Re: SUMIFS formula returns value 0

    Hello,

    So the original solution did help. However, when I downloaded additional data, the return value was the date (I've attached the sample).

    I must apologise that I am not a Microsoft Excel expert, and I am doing this out of sheer desperation as I am not getting any help. Could you please guide me on how to normalise data for subsequent datasets?

    I've tried copy-pasting the dates from the dataset that works; I've been attempting format painter; I've tried manually changing "/" in the dates to "-" - However it just doesn't work.

    The attached sheet has - data that works (Parth), data that doesn't work (akansha) and finally, a fresh download of the data (raw data with only deposit/withdrawal amount and remarks edited for privacy. The dates columns are AS IS) so you can grasp where this is going wrong.


    Thank you very much once again.
    I am from India, and my system settings are set to Indian locale.
    Attached Files Attached Files

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.86 for Mac MS 365
    Posts
    8,545

    Re: SUMIFS formula returns value 0

    I'm having trouble seeing where the problem is exactly, but here is a tip you can use. Anywhere you see the little green triangle in the upper right corner of a cell, click on that cell and move your cursor over it and a little yellow triangle will pop up with a dropdown arrow, click on the dropdown arrow and it will give you a list of possible issues, the one with the blue i beside it says it is a number stored as text. if you want to convert those to numbers, there are a couple different ways. one is using the data tab text to columns >> finish will usually convert dates from text to numbers. the "/" vs "-" you refer to is just a formatting style. Dates are stored in excel as numbers (unless they are imported as text or you format the cell they are in as text) so they are simply a numeric representation of a count of days since Jan 1st 1900 until today. to batch update cells to numbers a trick is you can put a 1 in an empty cell, then while on the cell click copy. Then highlight the entire range of cells you want to convert and (making sure they are formatted as general or number) right click, then pick paste special then from the dropdown pick multiply and they should all multiply by 1 converting to numbers.
    hope that helps.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  6. #6
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: SUMIFS formula returns value 0

    There is nothing wrong with the data and de formulas (only the cells on sheet calcalations are not correct formatted.)
    Please format cell B4:D4 as a number and the dates are chaniging in 0, 100 and 200 and that is the correct result.

    Note: At sheet ...parth are the dates in Column C still alphanumeric, but that doesn't care now, because these cells are not used by the calculations.

  7. #7
    Registered User
    Join Date
    08-07-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    8

    Re: SUMIFS formula returns value 0

    So your suggestion worked, including the text to the column, which showed no data until I used the error correction to convert the data to numbers.

    Is there an easier way? I'm a systems security expert doing this purely to keep my family finances afloat.

    Can I buy credits in the forum and have someone design what I want in Microsoft Excel? I am struggling since the same formula with different categories (so instead of salary, it is "groceries" or "dining", and I keep getting 0 again.)

+ 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] sumifs/sumproduct formula returns error or zero
    By adsako in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-26-2018, 03:06 PM
  2. [SOLVED] sumifs/sumproduct formula returns VALUE error
    By adsako in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-01-2018, 08:17 AM
  3. IFERROR(SUMIFS returns 0 and not the alternate formula?
    By mikey42979 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 02-24-2017, 11:00 AM
  4. USING SUMIFS formula returns a '0' for me
    By jlp83 in forum Excel General
    Replies: 7
    Last Post: 03-12-2015, 06:53 AM
  5. [SOLVED] sumifs formula returns error
    By kronikjb in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-08-2015, 12:51 PM
  6. Sumifs formula returns ZERO
    By Xx7 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-10-2013, 02:05 PM
  7. SUMIFS returns VALUE!
    By mogdog1981 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-03-2011, 04:38 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1