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.

 

[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

 

[awoohaw]

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