COUNTIF FORMULA WITHIN DATE RANGE.
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)))
Best Answers
-
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))
-
Hi @Gillian C
Thank you so much, works perfectly.
Answers
-
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
-
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.
-
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?
-
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.
-
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))
-
Hi @Gillian C
Thank you so much, works perfectly.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!