Countifs formula

ThedoreHolley

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.
 
Sort by date Sort by votes
So, tell me about the dates that did not get captured? Are those cell blank(absolutely nothing) or do they have a date of day 0 (Dec 31 1899)? What would be your rule for including and excluding the dates in the past. My first example went back 60 days - i'm guessing that specific value was incorrect, but it does demonstrate that there are ways to get other dates provided I have good criteria. Please let me know what that is.
 
Upvote 0

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
awoohaw said:
So, tell me about the dates that did not get captured? Are those cell blank(absolutely nothing) or do they have a date of day 0 (Dec 31 1899)? What would be your rule for including and excluding the dates in the past. My first example went back 60 days - i'm guessing that specific value was incorrect, but it does demonstrate that there are ways to get other dates provided I have good criteria. Please let me know what that is.
Click to expand...
So I need to capture all dates that have passed minus the empty slot. Since all this data is pulled from another sheet anything blank adds zero or the default date. But if a date is a legit date I need to account 60 and 90 day losses.
 
Upvote 0
okay. so regarding getting the date pulled from another sheet....
Is that done via a lookup or index of some kind? If so, you can update that pulling statement to give a value of "" instead of 0.
Can you share an example of the formula that pulls the date?

Secondly, can you confirm that when you say "dates have passed" you mean dates
1. WITHIN today() and Today()+60
or
2. WITHIN today() and Today()+90 (This will also include the records in the first group.
 
Upvote 0
awoohaw said:
okay. so regarding getting the date pulled from another sheet....
Is that done via a lookup or index of some kind? If so, you can update that pulling statement to give a value of "" instead of 0.
Can you share an example of the formula that pulls the date?

Secondly, can you confirm that when you say "dates have passed" you mean dates
1. WITHIN today() and Today()+60
or
2. WITHIN today() and Today()+90 (This will also include the records in the first group.
Click to expand...
So for pulling dates it is just and =sheetH2 for example. The dates that have pass would be if there was a 1 Feb 23 date for example. The formula would not currently count that in the 60 days. Which I need it to.
 
Upvote 0
1. To prevent the 0 date from showing up, you can put your date formula (assuming the sheet name is Sheet1) =if(Isblank(Sheet1!H2),"",Sheet1!H2)
2. The formula i gave uses the Today() function, which will change every 24 hours. Which is why 2/23 is no longer included as today is now 2/24. If you want to use a specific date then you either need to 'hard code' it into the formula (which is not recommended) or use a cell to house that comparison date. I asked if you wanted a the 60 counter to be starting from a month end. I also I asked if your defiition of 60 days is two months or a true 60 days (60 days from 31 Dec is 1 March not 28 Jan, for instance).
 
Upvote 0
You wrote: "The formula would not currently count that in the 60 days. Which I need it to." Please give me the EXACT method you use for wanting to include 23 Feb in the selection? Why do you consider 23 Feb between today() and today()+60, since Today() now equals 24Feb (as of the date of this post)? I don't think you want to use Today as your comparison value in the calculation, please tell me what you use.
 
Upvote 0
awoohaw said:
You wrote: "The formula would not currently count that in the 60 days. Which I need it to." Please give me the EXACT method you use for wanting to include 23 Feb in the selection? Why do you consider 23 Feb between today() and today()+60, since Today() now equals 24Feb (as of the date of this post)? I don't think you want to use Today as your comparison value in the calculation, please tell me what you use.
Click to expand...
I'm over thinking this problem the last formula you sent works perfectly. Technically I should never have anyone on the tracker pass their DEROS so you solved my problem. Thank you for your help and support!
 
Upvote 0
You're welcome. if you think one of the posts has provided you with a solution. please mark it as answered.

Best wishes!
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
COUNTIF Function and Blank Date Cells
2
Replies
12
Views
678
Joe4
Joe4
V
  • Solved
Countif, including today's date, excluding future dates
2
Replies
13
Views
408
Peter_SSs
Peter_SSs
D
  • Question
Update formula to check other conditions
Replies
1
Views
161
etaf
etaf
R
  • Solved
Ageing Bucket Formula
Replies
4
Views
407
Fluff
Fluff
A
  • Solved
NETWORKDAYS vs COUNTIFS
Replies
2
Views
225
amberd
A

Forum statistics

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

Share this page

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