Calculating the number of dates in a range - Countifs function not working

Bek T

Hi,

Quite a few similar questions on here

(eg https://community.smartsheet.com/discussion/55591/countifs-between-two-dates); however none of the solutions posted seem to be working; I'm still getting the 'invalid operation' message appearing.

I've got a sheet with numerous date entries, and I'd like to count those in the range for July, August, etc. and have in a sheet summary, so I can then run a report on the total submissions for each month.

I am currently trying this formula: =COUNTIFS([Date Received]1:[Date Received]16, >=DATE(2023, 7, 1), <=DATE(2023, 7, 31))

1) Preferably I'd have the 'date received' range for the whole column, in case of anything that isn't logged in order - to do this would I need an 'if error' function in there too?

2) The date received is a date select column, so would the date need to reflect what that is actually showing as (in this case it would be for example, 20/07/23)

3) Do I need to convert to a date value (as per this article regarding excel formulas, unfortunately which also doesn't work

=SUMPRODUCT((B14:B17>=DATEVALUE("1/1/2010"))*(B14:B17<=DATEVALUE("12/31/2010")))

Counts the number of cells in the range B14:B17 that are between (inclusive) 1/1/2010 and 12/31/2010 (3). This example serves as a substitute for the COUNTIFS function that was introduced in Excel 2007. The DATEVALUE function converts the dates to a numeric value, which the SUMPRODUCT function can then work with.

It's the end of the day for me so it may be something glaringly obvious that I'm overlooking, however appreciate any help!

Ngā mihi

Bek T

Some more googling and I seem to have it - have used the COUNTIF function, as opposed to SUMIF - so, to count the number of dates in July that are in my column called 'Date Received', I've used the below:

=COUNTIF([Date Received]:[Date Received], IFERROR(MONTH(@cell), 0) = 7)

Jason Albrecht

Hi @Bek T

Would something like this work for you?

This pic is from my sheet summary where I can change the dates to suit (be it yearly, quarterly, monthly, etc.).

Bek T

Hi @Jason Albrecht ,

Thanks for that. Tried, however I'm getting the #invalid operation error message appearing. I'm wondering if it's due to having a couple of dates missing from the range? May have to try adding an 'if error' function in there too. I do like the way you've set yours up though, nice alternative :)

Thanks

Bek T

Some more googling and I seem to have it - have used the COUNTIF function, as opposed to SUMIF - so, to count the number of dates in July that are in my column called 'Date Received', I've used the below:

=COUNTIF([Date Received]:[Date Received], IFERROR(MONTH(@cell), 0) = 7)