Countifs formula

A

AlanY

Well-known Member

Joined

Oct 30, 2014

Messages

4,527

Office Version

1. 365
2. 2019
3. 2016

Platform

1. Windows

• Feb 22, 2023

• #2

try

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

 

Upvote 0

awoohaw

Well-known Member

Joined

Mar 23, 2022

Messages

4,835

Office Version

1. 365

Platform

1. Windows
2. Web

• Feb 22, 2023

• #3

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

 

Upvote 0

Fluff

MrExcel MVP, Moderator

Joined

Jun 12, 2014

Messages

90,849

Office Version

1. 365

Platform

1. Windows

• Feb 22, 2023

• #4

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

 

Upvote 0

awoohaw

Well-known Member

Joined

Mar 23, 2022

Messages

4,835

Office Version

1. 365

Platform

1. Windows
2. Web

• Feb 22, 2023

• #5

if you have an older version of excel try this:

Excel Formula:

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

 

Upvote 0

ThedoreHolley

Board Regular

Joined

Sep 26, 2016

Messages

51

Office Version

1. 365

Platform

1. Windows

• Feb 22, 2023

• #6

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

 

Attachments

• 
  EXCEL.PNG

  85.1 KB · Views: 10

Upvote 0

awoohaw

Well-known Member

Joined

Mar 23, 2022

Messages

4,835

Office Version

1. 365

Platform

1. Windows
2. Web

• Feb 22, 2023

• #7

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

 

Upvote 0

ThedoreHolley

Board Regular

Joined

Sep 26, 2016

Messages

51

Office Version

1. 365

Platform

1. Windows

• Feb 22, 2023

• #8

awoohaw said:

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

Click to expand...

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

 

Upvote 0

ThedoreHolley

Board Regular

Joined

Sep 26, 2016

Messages

51

Office Version

1. 365

Platform

1. Windows

• Feb 22, 2023

• #9

ThedoreHolley said:

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

Click to expand...

I also tried the formula which all have returned 0

 

Upvote 0

awoohaw

Well-known Member

Joined

Mar 23, 2022

Messages

4,835

Office Version

1. 365

Platform

1. Windows
2. Web

• Feb 22, 2023

• #10

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

 

Upvote 0