countifs function; need to set a date range as a criteria

marco10 · May 29, 2020

Hi guys,
so i am trying to use countifs function to count number of people with multiple criteria.

this is the formula i am using:
=-COUNTIFS($C:$C,"Controller",$D:$D,"temp",$E:$E,"may-20",$F:$F, "may-20")
[(=-countifs(criteria range1,"criteria1",criteria range2,"criteria2",criteria range3,"criteria3",criteria range4,"criteria4")]
this uses the person's job role, contract type, start date and end date as criteria.

Previously, it looked like it was working well but then i realized if the start date is anything different than 01-may-20 similarly the end date if anything different from 01-may-20, it won't count as the criteria is not fulfilled and i get 0 as answer.
I tried using ">=may-20" and also changed to "=>may-20" but it would only consider the sign exactly infront of may; sign at the very beginning of the criteria is ignored.
"=01-may-20:31-may-20" tried this but was unsuccessful to achieve the desired result.
in the above experiment date was set to 15-may-20.
When ">" sign is used all the date of the month are counted but 01-may-20 will be ignored.
I need a way to put the date range as a criteria so that any date of that particular month is okay to fulfill the criteria.

will be grateful for the help, thank you.

marco10 · May 29, 2020

@Dave87 @joeu2004 apologies for disturbing you guys but help please.

joeu2004 · May 29, 2020

It sounds like you want to count all of the rows where the start date __and__ end date are in the same month, namly May 2020. Right?

And it sounds like the data in columns E and F are bona fide dates, not text. Right?

Confirm that ISNUMBER(E1) and ISNUMBER(F1) both return TRUE in all of the rows to be counted.

To that end, the following should work:

=-COUNTIFS(C1:C1000, "Controller", D1:D1000, "temp", E1:E1000, ">=" & DATE(2020,5,1), F1:F1000, "<" & DATE(2020,6,1))

In other words, count each row where column C is "controller", __and__ column D is "temp", __and__ the value in column E is >= DATE(2020,5,1), __and__ the value in column F is < DATE(2020,6,1).

Please note some subtle unrelated changes:

1. I use a limited range like C1:C1000 instead of a whole-column reference like C:C. Change C1 and C1000 to first and last cells where you might reasonably expect relevant data now and in the future. With a whole-column range like C:C, Excel might check as many as 1+ million rows. I presume that it is not "reasonable" to expect that many rows of data.

2. All conditions are AND'ed. If, instead, you want to count were a May date is in either column E __or__ column F, not necessarily both, you need very different formulas. LMK.

3. I use DATE(2020,5,1) instead of May-20, for example, to unambiguously refer to the dates that you want.

If that does not work for you, provide example data and corresponding COUNTIFS formula. As far as I'm concerned, you can upload an example file that demonstrates the problem to a file-sharing website, and post the download URL. Others active contributes might prefer that you use XL2BB feature in this forum.

Jerrod Conomy · May 29, 2020

Personally I would break it up into modular steps to separate the date filtering step from the countif function. For example, set a flag to 1 if within a date range then use countif to process a much simpler query (countif x = 1).

This will need more code but it will take less time to prepare and it will also allow for easy modification of the code in the future. Less code is not always the best but good luck with whichever path you take.

joeu2004 · May 29, 2020

joeu2004 said:
=-COUNTIFS(C1:C1000, "Controller", D1:D1000, "temp", E1:E1000, ">=" & DATE(2020,5,1), F1:F1000, "<" & DATE(2020,6,1))

The above works for May 2020. Equivalently, we could write "<=" & DATE(2020,5,31) for the last condition.

And the following demonstrates how to do it if we want the date to be a cell reference.

Book1

A

B

C

D

E

F

1

-1

May-2020

controller

03-Jan-2020

28-May-2020

2

-1

Jun-2020

controller

09-Feb-2020

27-Aug-2020

3

controller

29-May-2020

31-May-2020

4

controller

25-May-2020

01-Dec-2020

5

temp

24-Jan-2020

21-Sep-2020

6

temp

22-Jan-2020

12-May-2020

7

temp

18-May-2020

23-May-2020

8

temp

20-May-2020

06-Dec-2020

9

controller

temp

20-Apr-2020

27-Jul-2020

10

controller

temp

03-Jun-2020

23-Jun-2020

11

controller

temp

01-May-2020

29-Sep-2020

12

controller

temp

26-May-2020

30-May-2020

Sheet1

Rich (BB code):

Formulas:
A1: =-COUNTIFS($C$1:$C$1000, "Controller", $D$1:$D$1000, "temp", $E$1:$E$1000, ">=" & B1, $F$1:$F$1000, "<=" & EOMONTH(B1,0))
A2: =-COUNTIFS($C$1:$C$1000, "Controller", $D$1:$D$1000, "temp", $E$1:$E$1000, ">=" & B2, $F$1:$F$1000, "<=" & EOMONTH(B2,0))

B1 and B2 are first-of-the-month dates, to wit: 1-May-2020 and 1-June-2020. They are formatted to display May-2020 and Jun-2020.

marco10 · Jun 2, 2020

first of all, Apologies for my delayed response had to make some repairs.
thank you so much for helping me through you busy schedule.
to make it easier to discuss i have downloaded xl2bb to post the range rather than the photo.
so its like a data entry sheet and then an output sheet as follows.

updated crossed out base of Pre-contract staff database 06-04-20.xlsx

B

C

D

E

F

1

last name

Role

temp/perm

start date

end date

100

X

Estimating Manager-Civil

Temp

Jan-20

Jun-20

101

Y

Estimating Manager-Civil

Temp

Mar-20

Jun-20

102

Z

Estimating Manager-Civil

Temp

Jan-20

May-20

Staff Entry (civil & rail only)

and now the output sheet:

updated crossed out base of Pre-contract staff database 06-04-20.xlsx

C

D

E

F

G

H

I

J

K

L

M

N

O

1

Temp staff

2

Job roles

Jan-20

Feb-20

Mar-20

Apr-20

May-20

Jun-20

Jul-20

Aug-20

Sep-20

Oct-20

Nov-20

Dec-20

3

Estimating Manager-Civil

Sheet1

Cell Formulas
Range	Formula
D3	D3	=-COUNTIFS('Staff Entry (civil & rail only)'!$C:$C,"Estimating Manager-Civil",'Staff Entry (civil & rail only)'!$D:$D,"temp",'Staff Entry (civil & rail only)'!$E:$E,"jan-20",'Staff Entry (civil & rail only)'!$F:$F, ">jan-20")
E3	E3	=-COUNTIFS('Staff Entry (civil & rail only)'!$C:$C,"Estimating Manager-Civil",'Staff Entry (civil & rail only)'!$D:$D,"temp",'Staff Entry (civil & rail only)'!$E:$E,"<=feb-20",'Staff Entry (civil & rail only)'!$F:$F, ">feb-20")
F3	F3	=-COUNTIFS('Staff Entry (civil & rail only)'!$C:$C,"Estimating Manager-Civil",'Staff Entry (civil & rail only)'!$D:$D,"temp",'Staff Entry (civil & rail only)'!$E:$E,"<=Mar-20",'Staff Entry (civil & rail only)'!$F:$F, ">mar-20")
G3	G3	=-COUNTIFS('Staff Entry (civil & rail only)'!$C:$C,"Estimating Manager-Civil",'Staff Entry (civil & rail only)'!$D:$D,"temp",'Staff Entry (civil & rail only)'!$E:$E,"<=apr-20",'Staff Entry (civil & rail only)'!$F:$F, ">apr-20")
H3	H3	=-COUNTIFS('Staff Entry (civil & rail only)'!$C:$C,"Estimating Manager-Civil",'Staff Entry (civil & rail only)'!$D:$D,"temp",'Staff Entry (civil & rail only)'!$E:$E,"<=may-20",'Staff Entry (civil & rail only)'!$F:$F, ">may-20")
I3	I3	=-COUNTIFS('Staff Entry (civil & rail only)'!$C:$C,"Estimating Manager-Civil",'Staff Entry (civil & rail only)'!$D:$D,"temp",'Staff Entry (civil & rail only)'!$E:$E,"<=jun-20",'Staff Entry (civil & rail only)'!$F:$F, ">jun-20")
J3	J3	=-COUNTIFS('Staff Entry (civil & rail only)'!$C:$C,"Estimating Manager-Civil",'Staff Entry (civil & rail only)'!$D:$D,"temp",'Staff Entry (civil & rail only)'!$E:$E,"<=jul-20",'Staff Entry (civil & rail only)'!$F:$F, ">jul-20")
K3	K3	=-COUNTIFS('Staff Entry (civil & rail only)'!$C:$C,"Estimating Manager-Civil",'Staff Entry (civil & rail only)'!$D:$D,"temp",'Staff Entry (civil & rail only)'!$E:$E,"<=aug-20",'Staff Entry (civil & rail only)'!$F:$F, ">aug-20")
L3	L3	=-COUNTIFS('Staff Entry (civil & rail only)'!$C:$C,"Estimating Manager-Civil",'Staff Entry (civil & rail only)'!$D:$D,"temp",'Staff Entry (civil & rail only)'!$E:$E,"<=sep-20",'Staff Entry (civil & rail only)'!$F:$F, ">sep-20")
M3	M3	=-COUNTIFS('Staff Entry (civil & rail only)'!$C:$C,"Estimating Manager-Civil",'Staff Entry (civil & rail only)'!$D:$D,"temp",'Staff Entry (civil & rail only)'!$E:$E,"<=oct-20",'Staff Entry (civil & rail only)'!$F:$F, ">oct-20")
N3	N3	=-COUNTIFS('Staff Entry (civil & rail only)'!$C:$C,"Estimating Manager-Civil",'Staff Entry (civil & rail only)'!$D:$D,"temp",'Staff Entry (civil & rail only)'!$E:$E,"<=nov-20",'Staff Entry (civil & rail only)'!$F:$F, ">nov-20")
O3	O3	=-COUNTIFS('Staff Entry (civil & rail only)'!$C:$C,"Estimating Manager-Civil",'Staff Entry (civil & rail only)'!$D:$D,"temp",'Staff Entry (civil & rail only)'!$E:$E,"<=dec-20",'Staff Entry (civil & rail only)'!$F:$F, ">=dec-20")

this is the very initial work done by me which was fine until i realized that it won't work if the date in cell E of data entry is more than 01-Jan-2020 and also if the date in cell F is exactly 01-any month-2020; i:e has to be after 1st of any month for the formula to work.

marco10 · Jun 2, 2020

now without changing anything in the data entry sheet i made a change in the output sheet; i:e for the month of jan however, the result is 0.
the result:

updated crossed out base of Pre-contract staff database 06-04-20.xlsx

C

D

E

F

G

H

I

J

K

L

M

N

O

1

Temp staff

2

Job roles

Jan-20

Feb-20

Mar-20

Apr-20

May-20

Jun-20

Jul-20

Aug-20

Sep-20

Oct-20

Nov-20

Dec-20

3

Estimating Manager-Civil

Sheet1

Cell Formulas
Range	Formula
D3	D3	=-COUNTIFS('Staff Entry (civil & rail only)'!$C:$C,"Estimating Manager-Civil",'Staff Entry (civil & rail only)'!$D:$D,"temp",'Staff Entry (civil & rail only)'!$E:$E,">=" & DATE(2020,1,1),'Staff Entry (civil & rail only)'!$F:$F, "<" & DATE(2020,2,1))
E3	E3	=-COUNTIFS('Staff Entry (civil & rail only)'!$C:$C,"Estimating Manager-Civil",'Staff Entry (civil & rail only)'!$D:$D,"temp",'Staff Entry (civil & rail only)'!$E:$E,"<=feb-20",'Staff Entry (civil & rail only)'!$F:$F, ">feb-20")
F3	F3	=-COUNTIFS('Staff Entry (civil & rail only)'!$C:$C,"Estimating Manager-Civil",'Staff Entry (civil & rail only)'!$D:$D,"temp",'Staff Entry (civil & rail only)'!$E:$E,"<=Mar-20",'Staff Entry (civil & rail only)'!$F:$F, ">mar-20")
G3	G3	=-COUNTIFS('Staff Entry (civil & rail only)'!$C:$C,"Estimating Manager-Civil",'Staff Entry (civil & rail only)'!$D:$D,"temp",'Staff Entry (civil & rail only)'!$E:$E,"<=apr-20",'Staff Entry (civil & rail only)'!$F:$F, ">apr-20")
H3	H3	=-COUNTIFS('Staff Entry (civil & rail only)'!$C:$C,"Estimating Manager-Civil",'Staff Entry (civil & rail only)'!$D:$D,"temp",'Staff Entry (civil & rail only)'!$E:$E,"<=may-20",'Staff Entry (civil & rail only)'!$F:$F, ">may-20")
I3	I3	=-COUNTIFS('Staff Entry (civil & rail only)'!$C:$C,"Estimating Manager-Civil",'Staff Entry (civil & rail only)'!$D:$D,"temp",'Staff Entry (civil & rail only)'!$E:$E,"<=jun-20",'Staff Entry (civil & rail only)'!$F:$F, ">jun-20")
J3	J3	=-COUNTIFS('Staff Entry (civil & rail only)'!$C:$C,"Estimating Manager-Civil",'Staff Entry (civil & rail only)'!$D:$D,"temp",'Staff Entry (civil & rail only)'!$E:$E,"<=jul-20",'Staff Entry (civil & rail only)'!$F:$F, ">jul-20")
K3	K3	=-COUNTIFS('Staff Entry (civil & rail only)'!$C:$C,"Estimating Manager-Civil",'Staff Entry (civil & rail only)'!$D:$D,"temp",'Staff Entry (civil & rail only)'!$E:$E,"<=aug-20",'Staff Entry (civil & rail only)'!$F:$F, ">aug-20")
L3	L3	=-COUNTIFS('Staff Entry (civil & rail only)'!$C:$C,"Estimating Manager-Civil",'Staff Entry (civil & rail only)'!$D:$D,"temp",'Staff Entry (civil & rail only)'!$E:$E,"<=sep-20",'Staff Entry (civil & rail only)'!$F:$F, ">sep-20")
M3	M3	=-COUNTIFS('Staff Entry (civil & rail only)'!$C:$C,"Estimating Manager-Civil",'Staff Entry (civil & rail only)'!$D:$D,"temp",'Staff Entry (civil & rail only)'!$E:$E,"<=oct-20",'Staff Entry (civil & rail only)'!$F:$F, ">oct-20")
N3	N3	=-COUNTIFS('Staff Entry (civil & rail only)'!$C:$C,"Estimating Manager-Civil",'Staff Entry (civil & rail only)'!$D:$D,"temp",'Staff Entry (civil & rail only)'!$E:$E,"<=nov-20",'Staff Entry (civil & rail only)'!$F:$F, ">nov-20")
O3	O3	=-COUNTIFS('Staff Entry (civil & rail only)'!$C:$C,"Estimating Manager-Civil",'Staff Entry (civil & rail only)'!$D:$D,"temp",'Staff Entry (civil & rail only)'!$E:$E,"<=dec-20",'Staff Entry (civil & rail only)'!$F:$F, ">=dec-20")

once again thank you @joeu2004 and @Jerrod Conomy, i really appreciate your help and am grateful for the continuous support that you experts provide to this community.
have a good day, stay safe. : )

marco10 · Jun 10, 2020

@joeu2004 hi there, i would be grateful if you could help me out in this problem; thank you

countifs function; need to set a date range as a criteria

marco10

New Member

Excel Facts

marco10

New Member

joeu2004

Banned user

Jerrod Conomy

New Member

joeu2004

Banned user

marco10

New Member

marco10

New Member

marco10

New Member

Similar threads

Forum statistics

Share this page

countifs function; need to set a date range as a criteria

New Member

Excel Facts

New Member

Banned user

New Member

Banned user

New Member

New Member

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