Countifs formula between 2 date ranges is giving me a zero.

Tsteven4 · Aug 17, 2021

=COUNTIFS($G$5:$K$134;">11/30/2020";$G$5:$K$134;"<1/1/2021")
Why is this formula giving a zero.

18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	25-Jan-21	4-Feb-21	23-Mar-21	29-Mar-21
18-Jan-21	25-Jan-21	4-Feb-21	23-Mar-21	29-Mar-21
3-Dec-20	1-Jan-21	22-Jan-21	19-Apr-21	10-Jun-21
11-Nov-20	4-Dec-20	22-Feb-21	18-Mar-21	10-Jun-21
18-Dec-20	8-Mar-21	8-Mar-21	2-Apr-21	14-Apr-21
2-Dec-20	13-Jan-21	3-Feb-21	17-Mar-21	28-Apr-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
5-Jul-20	14-Jul-20	15-Jul-20		10-Aug-20
10-Jul-20	16-Jul-20	17-Jul-20		12-Aug-20
10-Nov-20	20-Nov-20	10-Dec-20		20-Jan-21
20-Nov-20	1-Dec-20	10-Dec-20		20-Jan-21
20-Nov-20	1-Dec-20	10-Dec-20		20-Jan-21
20-Nov-20	1-Dec-20	10-Dec-20		20-Jan-21
20-Nov-20	1-Dec-20	10-Dec-20		20-Jan-21
20-Nov-20	1-Dec-20	10-Dec-20		20-Jan-21
20-Nov-20	1-Dec-20	10-Dec-20		20-Jan-21
20-Nov-20	1-Dec-20	10-Dec-20		30-Jan-21
10-Dec-20	20-Dec-20	5-Jan-21		30-Jan-21
10-Dec-20	20-Dec-20	5-Jan-21		30-Jan-21
10-Dec-20	20-Dec-20	5-Jan-21		30-Jan-21
10-Dec-20	20-Dec-20	5-Jan-21		30-Jan-21
10-Nov-20	20-Nov-20	10-Dec-20		20-Jan-21
18-Jan-21	25-Jan-21	4-Feb-21	23-Mar-21	29-Mar-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Dec-20	8-Mar-21	8-Mar-21	2-Apr-21	14-Apr-21
2-Dec-20	13-Jan-21	3-Feb-21	17-Mar-21	28-Apr-21
10-Nov-20	20-Nov-20	10-Dec-20		20-Jan-21
10-Nov-20	20-Nov-20	10-Dec-20		20-Jan-21
10-Nov-20	20-Nov-20	10-Dec-20		20-Jan-21
10-Nov-20	20-Nov-20	10-Dec-20		20-Jan-21
10-Nov-20	20-Nov-20	10-Dec-20		20-Jan-21
10-Nov-20	20-Nov-20	10-Dec-20		20-Jan-21
10-Nov-20	20-Nov-20	10-Dec-20		20-Jan-21
10-Nov-20	20-Nov-20	10-Dec-20		20-Jan-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
	15-Oct-21	15-Nov-21		10-Dec-21
	15-Oct-21	15-Nov-21		10-Dec-21
	15-Oct-21	15-Nov-21		10-Dec-21
	15-Oct-21	15-Nov-21		10-Dec-21
	15-Oct-21	15-Nov-21		10-Dec-21
	15-Oct-21	15-Nov-21		10-Dec-21
	15-Oct-21	15-Nov-21		10-Dec-21
	15-Oct-21	15-Nov-21		10-Dec-21
	15-Oct-21	15-Nov-21		10-Dec-21
	15-Oct-21	15-Nov-21		10-Dec-21
	15-Oct-21	15-Nov-21		10-Dec-21
	15-Oct-21	15-Nov-21		10-Dec-21
	15-Oct-21	15-Nov-21		10-Dec-21
	15-Oct-21	15-Nov-21		10-Dec-21
	15-Oct-21	15-Nov-21		10-Dec-21
	15-Oct-21	15-Nov-21		10-Dec-21
	15-Oct-21	15-Nov-21		10-Dec-21
	15-Oct-21	15-Nov-21		10-Dec-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21
18-Jan-21	21-Feb-21	15-Mar-21	6-Apr-21	9-Aug-21

FormR · Aug 17, 2021

Do your regional settings expect dates to be a "dd/mm/yyyy" format?

=COUNTIFS($G$5:$K$134;">30/11/2020";$G$5:$K$134;"<1/1/2021")

Or you could try a unambiguous format of.

=COUNTIFS($G$5:$K$134;">2020-11-30";$G$5:$K$134;"<2021-01-01")

Or you could try using the date formula.

=COUNTIFS($G$5:$K$134;">" & Date(2020,11,30);$G$5:$K$134;"<" & date(2021,1,1))

Special-K99 · Aug 17, 2021

Try

=COUNTIFS($G$5:$K$134;">"&DATEVALUE("11/30/2020");$G$5:$K$134;"<"&DATEVALUE("01/01/2021")

etaf · Aug 17, 2021

are they actual dates in the cells or text
right click on a date
Format cells
Change to General and see if the date format changes to show just a number
dates are usually right justified and text is left, so looks like TEXT to me

44270 is the 15th March 21

Tsteven4 · Aug 17, 2021

This is the sample spreadsheet.

etaf said:
are they actual dates in the cells or text
right click on a date
Format cells
Change to General and see if the date format changes to show just a number
dates are usually right justified and text is left, so looks like TEXT to me

44270 is the 15th March 21

They are actual dates. Just the format of the cell is made to show the date in that way.

Marcelo Branco · Aug 17, 2021

It seems the dates are texts, not real dates (numbers).
If so try
=SUMPRODUCT(--(--$G$5:$K$134>DATE(2020,11,30)),--(--$G$5:$K$134<DATE(2021,1,1)))

M.

Tsteven4 · Aug 17, 2021

I have sorted out the problem, it was with the format of the date. Changed it in the formula to YYYYMMDD. Problem resolved.
Thanks all

Countifs formula between 2 date ranges is giving me a zero.

Tsteven4

New Member

Excel Facts

FormR

MrExcel MVP

Special-K99

Well-known Member

etaf

Well-known Member

Tsteven4

New Member

Marcelo Branco

MrExcel MVP

Tsteven4

New Member

Similar threads

Forum statistics

Share this page

Countifs formula between 2 date ranges is giving me a zero.

Tsteven4

New Member

Excel Facts

FormR

MrExcel MVP

Special-K99

Well-known Member

etaf

Well-known Member

Tsteven4

New Member

Marcelo Branco

MrExcel MVP

Tsteven4

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock