COUNTIF FORMULA WITHIN DATE RANGE.

Ed Gadd

Hi All,

I get invalid operation for the following formula. Any ideas on my error?

Trying to count the number of times something appears in a column within a date range.

Any help would be great please. Thanks.

=COUNTIFS({TIME RECORDING}, ([Column7]@row), AND(@cell >= DATE( [Column6]2), @cell <= DATE([Column6]@row)))

Gillian C

Hi @Ed Gadd

A comma between [Column7]@row and {TIME RECORDING DATE} should do it :)

=COUNTIFS({TIME RECORDING}, [Column7]@row,{TIME RECORDING DATE}, AND(@cell >= [Column6]2, @cell <= [Column6]1))

Ed Gadd

Hi @Gillian C

Thank you so much, works perfectly.

Gillian C

Hi @Ed Gadd

So looks like your formula is structured incorrectly.

Have a look at both Countifs

COUNTIFS Function | Smartsheet Learning Center

and Countif

COUNTIF Function | Smartsheet Learning Center

to see which one suits your needs.

Basically use Countifs if you have multiple criteria that you are needing to match. As an example: if you want to count the number of Blue Hats (from the [hat colour] column) that are sold within a certain time period ([Purchased Date]) then you would use Countifs, if you just wanted to count the number of hats that were blue then you would just use countif.

From your formula above is you are intenting to use countifs then you need to define the range of the second criteria relates to.

Hope that helps? If it's still giving you issues @ me and then I'll see if I can help more

Ed Gadd

Hi @Gillian C

Thanks for the quick reply. Yes I agree the formula is structured incorrectly. The COUNTIFS function is defiantly required here.

With reference to your message above I'd like to count the number of blue hats within a date range.

With reference to the formula I currently have [Column7]@row refers to blue hats

[Column6]2 refers to the initial date

Column6]@row refers to todays date

I suspect the (below) is the source of the error. This is part of the formula trying to identify the date range.

AND(@cell >= DATE( [Column6]2), @cell <= DATE([Column6]@row)))

Any help would be much appreciated.

Gillian C

Hi @Ed Gadd

I think you are needing to do something like this

=COUNTIFS([Colour of Hats]:[Colour of Hats], "Blue", [Purchased Date]:[Purchased Date], AND(@cell >= [Purchased Date]2, @cell <= [Purchased Date]@row))

I understand it probably feels like you are defining the range twice but really the bold is defining the range that you are looking in, and the AND() part is defining the range of value you are looking for in the range column…

You will need to change back to your actual column headers :)

Hope that helps?

Ed Gadd

Hi @Gillian C Thanks again for the quick reply.

I think this is getting close. Although I'm getting the response unparseable. Can you see any errors in the below?

=COUNTIFS({TIME RECORDING}, [Column7]@row {TIME RECORDING DATE}, AND(@cell >= [Column6]2, @cell <= [Column6]1))

Thank you.

Gillian C

Hi @Ed Gadd

A comma between [Column7]@row and {TIME RECORDING DATE} should do it :)

=COUNTIFS({TIME RECORDING}, [Column7]@row,{TIME RECORDING DATE}, AND(@cell >= [Column6]2, @cell <= [Column6]1))

Ed Gadd

Hi @Gillian C

Thank you so much, works perfectly.