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.

 

[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)))

 

[AlanY]

try

=COUNTIFS(J:J,">="&(TODAY()-60))

 

[awoohaw]

try:

Mr Excel 8.xlsm
	A									J	K
1	Name									Date	2023-02-22
2	Larken									2023-02-24	3
3	Levine									2023-03-14
4	Smith									2023-01-28
5	Lofton									2023-05-14
6	Lotus									2022-12-25
7	Jackson									2022-12-04
8	Leaf									2023-05-13
9
Sheet4

Cell Formulas
Range	Formula
K2	K2	=SUM((--(LEFT(A2:A8,1)="L"))*(--((J2:J8)>=(K1-60)))*(--((J2:J8)<=(K1+60))))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[awoohaw]

if you have an older version of excel try this:

=SUMPRODUCT((--(LEFT(A2:A8,1)="L"))*(--((J2:J8)>=(K1-60)))*(--((J2:J8)<=(K1+60))))

 

[ThedoreHolley]

So here id the formula I was using. =COUNTIFS($B$71:$B$88,B64,$G$71:$G$88,"<="&TODAY()+60)-COUNTIF($G$71:$G$88,"0-jan-00")

 

[awoohaw]

what cell is the formula in? and what is your expected output? Have you tried any of the possible solutions that have been posted?

 

[ThedoreHolley]

what cell is the formula in? and what is your expected output? Have you tried any of the possible solutions that have been posted?

The formula is in F 64. The outcome is total number of who are within 60 days by MOS(13J10, 20 ETC).

 

[ThedoreHolley]

The formula is in F 64. The outcome is total number of who are within 60 days by MOS(13J10, 20 ETC).

I also tried the formula which all have returned 0