COUNTIFS and SUMIFS with a Date Range

Jenna Corso

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

L_123

Your countifs formula returns a value for me. You might want to check your data type inside of your column 1.

Your sumifs formula is in the incorrect syntax. The summed column is referenced at the beginning not the end.

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

L_123

Your countifs formula returns a value for me. You might want to check your data type inside of your column 1.

Your sumifs formula is in the incorrect syntax. The summed column is referenced at the beginning not the end.

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

Jenna Corso

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

Jed Rusyniak

@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

L_123

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)