Countifs formula

ThedoreHolley

Board Regular
Joined
Sep 26, 2016
Messages
51
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
try

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

Mr Excel 8.xlsm
AJK
1NameDate2023-02-22
2Larken2023-02-243
3Levine2023-03-14
4Smith2023-01-28
5Lofton2023-05-14
6Lotus2022-12-25
7Jackson2022-12-04
8Leaf2023-05-13
9
Sheet4
Cell Formulas
RangeFormula
K2K2=SUM((--(LEFT(A2:A8,1)="L"))*(--((J2:J8)>=(K1-60)))*(--((J2:J8)<=(K1+60))))
 
Upvote 0
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
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
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
    EXCEL.PNG
    85.1 KB · Views: 10
Upvote 0
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
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).
 
Upvote 0
When you say within 60 days, is that 60 day before, after, or before and after the MOS Date?
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top