COUNTIFS with a Date Range
Hello there,
I am needing help with a formula, i am trying to count the number of faults that happen per week.
I have the below so far but it is coming up #UNPARSEABLE
=COUNTIFS([Faults]:[Faults], [Week Starting]@row >= [Week Ending]2 <=,[Week Starting]@row >= [Week Ending]2<=)
Best Answer
-
Hi @Leah Taylor ,
If you want the count of how many times there are faults then you would use this formula:
=COUNTIFS([Number of Faults]:[Number of Faults], >0, [Production Date]:[Production Date], AND(@cell >= [Week Starting]@row, @cell <= [Week Ending]@row))
If you wanted the sum of how many faults there are, then this formula would be what you're after:
=SUMIFS([Number of Faults]:[Number of Faults], [Production Date]:[Production Date], AND(@cell >= [Week Starting]@row, @cell <= [Week Ending]@row))
Sample:
Hope this helps. If you've any questions etc. then just ask! 😊
Answers
-
Hey @Leah Taylor
Is it possible to share a screenshot? I'm a bit confused at what your formula was trying to do so it would be helpful to see how your data is arranged.
Are you tracking data week to week, or only a snapshot of the current week? If only a snapshot of the current week then try this. Know that the WEEKNUMBER function begins a week on Monday. This cannot be changed.
=COUNTIFS([Faults]:[Faults], WEEKNUMBER([Week Starting]@row)=WEEKNUMBER(TODAY()))
Will this work for you? If not, please provide a screenshot
Kelly
-
Thanks for helping @Kelly Moore
I am tracking data week to week. Basically I have the number of faults listed for that production batch along with the date of the production. I am wanting to group the number of faults between the date ranges (Week Starting & Week Ending columns)
Does this make more sense? :-)
-
Hi @Leah Taylor ,
If you want the count of how many times there are faults then you would use this formula:
=COUNTIFS([Number of Faults]:[Number of Faults], >0, [Production Date]:[Production Date], AND(@cell >= [Week Starting]@row, @cell <= [Week Ending]@row))
If you wanted the sum of how many faults there are, then this formula would be what you're after:
=SUMIFS([Number of Faults]:[Number of Faults], [Production Date]:[Production Date], AND(@cell >= [Week Starting]@row, @cell <= [Week Ending]@row))
Sample:
Hope this helps. If you've any questions etc. then just ask! 😊
-
Hey @Leah Taylor
Thanks for the screenshot, it was exactly what I needed.
=COUNTIFS([Number of Faults]:[Number of Faults],1, [Production Date]:[Production Date], ISDATE(@cell), [Week Starting]:[Week Starting], [Production Date]@row>=@cell, [Week Ending]:[Week Ending], [Production Date]@row<=@cell)
Will this work for you?
Kelly
-
Thank you very much @Nick Korna and @Kelly Moore that worked perfect! :-D
Your help is very much appreciated!
Leah
-
I am now wanting to count the how many times there where no faults. Basically count the number of blank cells within the date ranges. I have added in the ISBLANK function but I don't think I'm using it correctly?
=COUNTIFS([Number of Faults]:[Number of Faults], ISBLANK([Number of Faults]@row)), >0, [Production Date]:[Production Date], AND(@cell >= [Week Starting]@row, @cell <= [Week Ending]@row))
Thank you!!
Leah
-
Hey @Leah Taylor
=COUNTIFS([Number of Faults]:[Number of Faults], ISBLANK(@cell), [Production Date]:[Production Date], AND(@cell >= [Week Starting]@row, @cell <= [Week Ending]@row))
Does this work for you?
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!