Countifs formula

[ThedoreHolley]

So I'm trying to count servals cells based off a date. For example If colum A is last name starts will the letter L . Count how many DOB in colum J are within 60 days from today. Some cells are blank and returns the default date 0-jan-00. Which I do not want to count these dates. Any help with a formula would be greatly appreciated.

 

[ThedoreHolley]

When you say within 60 days, is that 60 day before, after, or before and after the MOS Date?

Any date from today and 60 days away.

 

[awoohaw]

and what is the comparison date? Allways the current day or will you type the date for comparison into a cell? Or is an End of Month Date?

 

[awoohaw]

So today + 60, or is it 2 months?

 

[awoohaw]

this is 60 day inclusive audit:

Mr Excel 8.xlsm
	A	B	C	D	E	F	G	H	I
63		MOS	AUT	O/H	%	O/H	%	O/H	%
64		13J10	12	9	75%	2	50%	6	50%
65		13J20	4	2	50%	0
66		13J30
67		13J40
68
69
70	MTOE	MOS	Position	Rank	Name		DEROS
71	201	13J10					2023-08-23
72	202	13J40
73	202	13J20					2023-09-12
74	202	13J20
75	202	13J20
76	202	13J10					2023-03-13
77	202	13J10
78	202	13J10					2023-10-20
79	202	13J10
80	203	13J20					2023-06-30
81	203	13J20					2023-07-30
82	203	13J10					2023-11-29
83	203	13J10					2023-11-14
84	203	13J10					2024-01-19
85	203	13J10
86	203	13J10					2023-06-30
87	203	13J10					2023-02-20
88	203	13J10					2023-04-16
89
Sheet4

Cell Formulas
Range	Formula
F64	F64	=SUM((--(TODAY()+60>=$G$71:$G$88))*(--(TODAY()<=$G$71:$G$88))*(--($B$64=$B$71:$B$88)))
F65	F65	=SUM((--(TODAY()+60>=$G$71:$G$88))*(--(TODAY()<=$G$71:$G$88))*(--($B$65=$B$71:$B$88)))

 

[ThedoreHolley]

this is 60 day inclusive audit:

Mr Excel 8.xlsm
	A	B	C	D	E	F	G	H	I
63		MOS	AUT	O/H	%	O/H	%	O/H	%
64		13J10	12	9	75%	2	50%	6	50%
65		13J20	4	2	50%	0
66		13J30
67		13J40
68
69
70	MTOE	MOS	Position	Rank	Name		DEROS
71	201	13J10					2023-08-23
72	202	13J40
73	202	13J20					2023-09-12
74	202	13J20
75	202	13J20
76	202	13J10					2023-03-13
77	202	13J10
78	202	13J10					2023-10-20
79	202	13J10
80	203	13J20					2023-06-30
81	203	13J20					2023-07-30
82	203	13J10					2023-11-29
83	203	13J10					2023-11-14
84	203	13J10					2024-01-19
85	203	13J10
86	203	13J10					2023-06-30
87	203	13J10					2023-02-20
88	203	13J10					2023-04-16
89
Sheet4

Cell Formulas
Range	Formula
F64	F64	=SUM((--(TODAY()+60>=$G$71:$G$88))*(--(TODAY()<=$G$71:$G$88))*(--($B$64=$B$71:$B$88)))
F65	F65	=SUM((--(TODAY()+60>=$G$71:$G$88))*(--(TODAY()<=$G$71:$G$88))*(--($B$65=$B$71:$B$88)))

That formula is working to give the sum. Thanks a lot. Now I just will need to figure out to account for those dates that are before today such as the 20 FEB 23.

 

[awoohaw]

No, the formula counts. The multiplication process creates only 1s or 0s, 1 being if the three conditions are true. (MOS, Date < 60+Today, >=Today). By summing those 1's you get the count.

 

[awoohaw]

Did you verify that the formula I gave you counts and does not sum?

 

[ThedoreHolley]

Did you verify that the formula I gave you counts and does not sum?

It works, I just count the days that may have already passed. Which I think it would also count all the default days which I do not need it to do that.

 

[awoohaw]

the formula should only count dates for the particular MSO from Today to Today+60. I'm not sure what you mean by dates already passed.

 

[ThedoreHolley]

the formula should only count dates for the particular MSO from Today to Today+60. I'm not sure what you mean by dates already passed.

I'm probably going to have to create another column to account for those dates that have already passed. If you noticed it did not count the dates before today. That's was my major problem from the beginning with this product. Since I'm trying to count dates I have not figured out how not to count the default (0-jan-00) dates.
I may be confusing you but the formula you provided does do what I asked minus the above. Thank you.