SUMIFS formula returns value 0

[parthmaniar]

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?

[HansDouwe]

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".

[HansDouwe]

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.

[parthmaniar]

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.

[Sam Capricci]

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.

[HansDouwe]

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.

[parthmaniar]

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.)