countifs with date

[errollflynn]

Hello,

I've been banging my head against an issue I've been having with countifs. I have three ranges that I'm comparing.
The first column contains values 1-3 and I'm looking for 3. The second column contains the first day of a month i.e. 1/1/2021, 2/1/2021 etc. The third column also contains the first day of the month. I'm looking to count instances of where the first column has the value 3, the second has 6/1/2021 and the third has a date after the date in the second column. The formula I have is:

=COUNTIFS(Active!W3:W35,"3",Active!Z3:Z35,"6/1/2021",Active!AB3:AB35,">"&6/1/2021)

I'm getting 0 for a result. Based on how I understand the formula should work, row 9 should be counted.

COO Workforce Restoration Template MASTER 12.23.2020 JM.xlsx
	W	X	Y	Z	AA	AB
3	1
4	2		Cat 1	5/1/2021
5	1
6	1
7	1
8	2		Cat 1	7/1/2021
9	3		Cat 2	6/1/2021	Cat 1	7/1/2021
10	1
11	1
12	1
13	1
14	1
15	1
16	1
17	1
18	3		Cat 2	7/1/2021	Cat 1	7/1/2021
19	1
20	1
21	1
22	1
23	2		Cat 1	5/1/2021
24	2		Cat 1	8/1/2021
25	3		Cat 2	8/1/2021	Cat 1	8/1/2021
26	1
27	2		Cat 1	5/1/2021
28	1
29	2		Cat 1	7/1/2021
30	3		Cat 2	4/1/2021	Cat 1	6/1/2021
31	1
32	1
33	2		Cat 1	4/1/2021
34	1
35	0
Active

Cells with Data Validation
Cell	Allow	Criteria
Z21	List	='https://aegworldwide-my.sharepoint.com/personal/jmelendez_aegpresents_com/Documents/Mstr Cmpy Ovw 2020/WF Restoration Plan 2021/[Mkt Workforce Restoration Template MASTER 12.23.2020 BMK.xlsx]Date Criteria'!#REF!
Z4	List	='Date Criteria'!$A$2:$A$14
Z6	List	='Date Criteria'!$A$2:$A$14
Z13	List	='Date Criteria'!$A$2:$A$14
Z25:Z35	List	='Date Criteria'!$A$2:$A$14
AB11	List	='Date Criteria'!$A$2:$A$14
AB6	List	='Date Criteria'!$A$2:$A$14
Z15	List	='Date Criteria'!$A$2:$A$14
AB32:AB35	List	='Date Criteria'!$A$2:$A$14
AB9	List	='Date Criteria'!$A$2:$A$14
AB18	List	='Date Criteria'!$A$2:$A$14
AB25	List	='Date Criteria'!$A$2:$A$14
AB30	List	='Date Criteria'!$A$2:$A$14
Z8:Z9	List	='Date Criteria'!$A$2:$A$14
Z18	List	='Date Criteria'!$A$2:$A$14

Thank you for your help.

 

[denzo36]

I think your formula has a syntax error in the last argument - should it be the following:

=COUNTIFS(Active!W3:W35,"3",Active!Z3:Z35,"6/1/2021",Active!AB3:AB35,">6/1/2021")

 

[Peter_SSs]

Here is another approach you could use. Note that my dates are d/m/y format.

21 02 23.xlsm
	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1
2								3	1/06/2021	1
3	1
4	2		Cat 1	1/05/2021
5	1
6	1
7	1
8	2		Cat 1	1/07/2021
9	3		Cat 2	1/06/2021	Cat 1	1/07/2021
10	1
11	1
12	1
13	1
14	1
15	1
16	1
17	1
18	3		Cat 2	1/07/2021	Cat 1	1/07/2021
19	1
20	1
21	1
22	1
23	2		Cat 1	1/05/2021
24	2		Cat 1	1/08/2021
25	3		Cat 2	1/08/2021	Cat 1	1/08/2021
26	1
27	2		Cat 1	1/05/2021
28	1
29	2		Cat 1	1/07/2021
30	3		Cat 2	1/04/2021	Cat 1	1/06/2021
31	1
32	1
33	2		Cat 1	1/04/2021
34	1
35	0
36
Count

Cell Formulas
Range	Formula
AF2	AF2	=COUNT(FILTER(W3:W35,(W3:W35=AD2)*(Z3:Z35=AE2)*(AB3:AB35>AE2),""))

 

[Alex Blakenburg]

As far as I can tell your formula should work. How did you source your data ? Is there any chance it from somewhere using a different regional setting ?
If you type =month("6/1/2021") and =month(Z9) to they both return 6 ?
Do your dates have time as part of the date that is hidden by the formatting eg does =value(Z9) return a whole number or does it have a decimal component ?

 

[errollflynn]

As far as I can tell your formula should work. How did you source your data ? Is there any chance it from somewhere using a different regional setting ?
If you type =month("6/1/2021") and =month(Z9) to they both return 6 ?
Do your dates have time as part of the date that is hidden by the formatting eg does =value(Z9) return a whole number or does it have a decimal component ?

The dates are entered via a dropdown list created using validation. On the tab where the values for the dates exist, they are formatted as general. I changed them to text, and then chose the same date again and am still getting the same result. Both "=month..." versions above return 6. The dates do not have time hidden by formatting and were entered as "1/1/2021", "2/1/2021" etc. to create the dropdown list.

 

[errollflynn]

I

I think your formula has a syntax error in the last argument - should it be the following:

=COUNTIFS(Active!W3:W35,"3",Active!Z3:Z35,"6/1/2021",Active!AB3:AB35,">6/1/2021")

I initially had the argument set up that way and got the same result.

The odd thing is, if I remove the last part of the expression ",Active!AB3:AB35,">6/1/2021")" I get the expected result. I'm doing something wrong in the last portion.

 

[Alex Blakenburg]

The dates are entered via a dropdown list created using validation. On the tab where the values for the dates exist, they are formatted as general. I changed them to text, and then chose the same date again and am still getting the same result. Both "=month..." versions above return 6. The dates do not have time hidden by formatting and were entered as "1/1/2021", "2/1/2021" etc. to create the dropdown list.

I only mentioned Z9 and your reply to denzo36 narrows the problem down to column AB.
You definitely do not want the column formatted as text prior to inputting the data. In fact if =istext(AB9) returns TRUE that will be your problem.

 

[errollflynn]

I only mentioned Z9 and your reply to denzo36 narrows the problem down to column AB.
You definitely do not want the column formatted as text prior to inputting the data. In fact if =istext(AB9) returns TRUE that will be your problem.

Both Z9 and AB9 return TRUE. but when I highlight the cells, the number format shows "Date."

 

[errollflynn]

I only mentioned Z9 and your reply to denzo36 narrows the problem down to column AB.
You definitely do not want the column formatted as text prior to inputting the data. In fact if =istext(AB9) returns TRUE that will be your problem.

After some more research, I noticed there is a single quote displayed in front of the date in the formula bar. If I remove the single quote the formula works. The challenge now is even if I modify the dates accordingly on the tab with the dates for the dropdown list, I still have to "redo" all the dates on the spreadsheet. Sheesh.

 

[Alex Blakenburg]

After some more research, I noticed there is a single quote displayed in front of the date in the formula bar. If I remove the single quote the formula works. The challenge now is even if I modify the dates accordingly on the tab with the dates for the dropdown list, I still have to "redo" all the dates on the spreadsheet. Sheesh.

You don't need to do this manually. There are a number of ways to convert your text to numbers (which is how excel sees dates).
If you have nothing else in the column: select the whole column > Data > Text to columns > Delimited > Make sure only TAB is selected > Finish. Excel will effectively re-enter all your dates and in doing so convert them to a number/date.
If you have other things in the column select the specific cells you need to convert and follow the same steps, it should default the 1st cell as the destination and this should be fine.

You can also do it using paste special multiply (and have 1 in a cell that you use to copy before invoking the paste special)