Sumifs formula with a date

Faye Hilbourne

I'm trying to calculate a total value if two criteria are met using the sumifs formula but I keep getting a 0 result. I think the problem has to do with the format of the date field.

The formula is

=SUMIFS({Cost Tracker Testv1.2 Range 5}, {Cost Tracker Testv1.2 Range 4}, "Actual", {Cost Tracker Testv1.2 Range 7}, "01/04/22")

I know it can work as I changed the date "01/04/22" to "test" in the formula and in the original sheet and it returned a value.

The original sheet column is in the data format rather than text/number. I've also tried changing the / to . with no luck. Can anyone help? Thanks

Paul Newcome

To use a date in a formula, you have to use the DATE function.

=SUMIFS({Cost Tracker Testv1.2 Range 5}, {Cost Tracker Testv1.2 Range 4}, "Actual", {Cost Tracker Testv1.2 Range 7}, @cell = DATE(2022, 04, 01))

Paul Newcome

To use a date in a formula, you have to use the DATE function.

=SUMIFS({Cost Tracker Testv1.2 Range 5}, {Cost Tracker Testv1.2 Range 4}, "Actual", {Cost Tracker Testv1.2 Range 7}, @cell = DATE(2022, 04, 01))

Faye Hilbourne

Paul, thank you so much that's worked :)

Paul Newcome

Happy to help. 👍️