# COUNTIFS with a Date Range

Options
âœ­âœ­

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

Tags:

• âœ­âœ­âœ­âœ­âœ­âœ­
Options

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! ðŸ˜Š

• âœ­âœ­âœ­âœ­âœ­âœ­
edited 03/20/23
Options

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

• âœ­âœ­
Options

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? :-)

• âœ­âœ­âœ­âœ­âœ­âœ­
Options

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! ðŸ˜Š

• âœ­âœ­âœ­âœ­âœ­âœ­
Options

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

• âœ­âœ­
Options

Thank you very much @Nick Korna and @Kelly Moore that worked perfect! :-D

Your help is very much appreciated!

Leah

• âœ­âœ­
Options

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

• âœ­âœ­âœ­âœ­âœ­âœ­
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!