COUNTIFS with a Date Range

Leah Taylor

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

Nick Korna

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! 😊

Kelly Moore

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

Leah Taylor

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

Nick Korna

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! 😊

Kelly Moore

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

Leah Taylor

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

Your help is very much appreciated!

Leah

Leah Taylor

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

Kelly Moore

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