Excel COUNTIFS Multiple Criteria

0
votes

I can not figure out the proper formula for this.

I have columns A - L. Data goes from row 2 - 190. Column F has companies A - S. Column I has dates.

I want to count the number of times Company S shows in the same row with a date range of exactly 5 years before today and within 90 days of today.

The closest I can get is: =COUNTIFS(F2:F190,"S",I2:I190,"<=90"&TODAY())

Edit:

I hope this makes sense.

Rows 2, 3, 4 all have company S. Their open dates are 6/9/2016, 9/7/2011 0:00 , and 12/11/2007. The first date is well within 5 years from today so I dont need that counted, second date is within 5 years AND within 90 days of the 5 year mark so I DO want that counted. Third date is well outside the 5 year mark and not within 90 days so not counted.

So of those three dates the countifs should return 1

1
excel
Add example to clearify your aim. – bzimor
like =COUNTIFS(F2:F190,"S",I2:I190,"<="&(TODAY()-90),I2:I190,">="&(DATE(YEAR(TODAY())-5,MONTH(TODAY()),DAY(TODAY()))))? (please note that (long) code in comments sometimes contain hidden characters which may corrupt the code. pls check that if an error occures) – Dirk Reichel
One way to get rid of the hidden characters is, to paste the code into your vba-console (the hidden characters will transform to "?" which then can be deleted very easy) ;) – Dirk Reichel
Not clear what the constraint is. Do you want a range from 5 years ago to (today - 90 days), or do you want a range from 5 years ago to (5 years ago + 90 days). Initially, I thought the first case, but your comment seems to imply otherwise. – David Zemens
5 years ago to (today - 90 days). Sorry if I wasnt clear – Derek E

1 Answers

0
votes

You can try with the below formula

=SUMPRODUCT((F2:F190="S")*((TODAY()-I2:I190)>=90)*(I2:I190>=DATE(YEAR(TODAY())-5,MONTH(TODAY()),DAY(TODAY()))))