# COUNTIFS and SUMIFS with a Date Range

Hello,

I'm trying to count the number of cells that have a date in the year 2020. This is the formula that is returning "0" which is incorrect:

=COUNTIFS([Column 1]:[Column 1], <=DATE(2020, 12, 31), [Column 1]:[Column 1], >=DATE(2020, 1, 1))

Additionally, I want to sum the values that correspond to this date range, and this formula is returning an invalid operation error:

=SUMIFS([Column 1]:[Column 1], <=DATE(2020, 12, 31), [Column 1]:[Column 1], >=DATE(2020, 1, 1), [Proceeds Amount]:[Proceeds Amount])

Tags:

• You are correct on both! Thank you very much for your help!

• @L@123 Would love your input on my sheet and formula here. I have a very similar issue, however I'm not able to get the data to pull in correctly.

I want to be able to sum the [Total Cost] for each type of special project by month using the date range like you had explained. The formula would need to look at the [Special Project Type] column then sum [Total Cost] if within the specified date range [Created]. Your assistance is greatly appreciated.

=SUMIFS([Proceeds amount]:[Proceeds amount], [Column 1]:[Column 1], <=DATE(2020, 12, 31), [Column 1]:[Column 1], >=DATE(2020, 1, 1))

=SUMIFS({type}, ="Pallet Tracking", {date}, <=DATE(2020, 3, 31), {date}, >DATE(2020, 3, 1), {total cost}) - Returns “Invalid Operation”

=SUMIFS({total charge}, {daterange}, <=DATE(2020, 4, 30), {date range}, >=DATE(2020, 4, 1)) - Returns “Invalid Reference”

=SUMIF({type}, ="Pallet Tracking", {total cost}) - Returns a sum of all revenue from pallet usage

• Apologies for the delay, i've been away for covid reasons.

1.

=SUMIFS([Proceeds amount]:[Proceeds amount], [Column 1]:[Column 1], <=DATE(2020, 12, 31), [Column 1]:[Column 1], >=DATE(2020, 1, 1))

This is formatted correctly. Generally I prefer to place an @cell reference instead of leaving statements open ended such as @cell <= Date(2020, 12, 31) as it is a good practice to get into, but it should still work.

2.

=SUMIFS({type}, ="Pallet Tracking", {date}, <=DATE(2020, 3, 31), {date}, >DATE(2020, 3, 1), {total cost})

{total cost} should be at the beginning of the statement rather than the end. If this doesn't fix the problem you should double check that your ranges are actually set, you can do this by putting your cursor somewhere in the reference then select "Edit Reference" on the dropdown. I would check each of them to make sure they reference the correct data set. They must each also contain the same amount of cells.

=SUMIFS({total cost}, {type}, ="Pallet Tracking", {date}, <=DATE(2020, 3, 31), {date}, >DATE(2020, 3, 1))

3.

=SUMIFS({total charge}, {daterange}, <=DATE(2020, 4, 30), {date range}, >=DATE(2020, 4, 1))

Formatted correctly, make sure that there are no error in the data, and make sure your references are correct (Same as in answer 2)

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!