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

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
and what is the comparison date? Allways the current day or will you type the date for comparison into a cell? Or is an End of Month Date?
 
Upvote 0
this is 60 day inclusive audit:

Mr Excel 8.xlsm
ABCDEFGHI
63MOSAUTO/H%O/H%O/H%
6413J1012975%250%650%
6513J204250%0
6613J30
6713J40
68
69
70MTOEMOSPositionRankNameDEROS
7120113J102023-08-23
7220213J40
7320213J202023-09-12
7420213J20
7520213J20
7620213J102023-03-13
7720213J10
7820213J102023-10-20
7920213J10
8020313J202023-06-30
8120313J202023-07-30
8220313J102023-11-29
8320313J102023-11-14
8420313J102024-01-19
8520313J10
8620313J102023-06-30
8720313J102023-02-20
8820313J102023-04-16
89
Sheet4
Cell Formulas
RangeFormula
F64F64=SUM((--(TODAY()+60>=$G$71:$G$88))*(--(TODAY()<=$G$71:$G$88))*(--($B$64=$B$71:$B$88)))
F65F65=SUM((--(TODAY()+60>=$G$71:$G$88))*(--(TODAY()<=$G$71:$G$88))*(--($B$65=$B$71:$B$88)))
 
Upvote 1
Solution
awoohaw said:
this is 60 day inclusive audit:

Mr Excel 8.xlsm
ABCDEFGHI
63MOSAUTO/H%O/H%O/H%
6413J1012975%250%650%
6513J204250%0
6613J30
6713J40
68
69
70MTOEMOSPositionRankNameDEROS
7120113J102023-08-23
7220213J40
7320213J202023-09-12
7420213J20
7520213J20
7620213J102023-03-13
7720213J10
7820213J102023-10-20
7920213J10
8020313J202023-06-30
8120313J202023-07-30
8220313J102023-11-29
8320313J102023-11-14
8420313J102024-01-19
8520313J10
8620313J102023-06-30
8720313J102023-02-20
8820313J102023-04-16
89
Sheet4
Cell Formulas
RangeFormula
F64F64=SUM((--(TODAY()+60>=$G$71:$G$88))*(--(TODAY()<=$G$71:$G$88))*(--($B$64=$B$71:$B$88)))
F65F65=SUM((--(TODAY()+60>=$G$71:$G$88))*(--(TODAY()<=$G$71:$G$88))*(--($B$65=$B$71:$B$88)))
Click to expand...
That formula is working to give the sum. Thanks a lot. Now I just will need to figure out to account for those dates that are before today such as the 20 FEB 23.
 
Upvote 0
No, the formula counts. The multiplication process creates only 1s or 0s, 1 being if the three conditions are true. (MOS, Date < 60+Today, >=Today). By summing those 1's you get the count.
 
Upvote 0
Did you verify that the formula I gave you counts and does not sum?
 
Upvote 0
awoohaw said:
Did you verify that the formula I gave you counts and does not sum?
Click to expand...
It works, I just count the days that may have already passed. Which I think it would also count all the default days which I do not need it to do that.
 
Upvote 0
the formula should only count dates for the particular MSO from Today to Today+60. I'm not sure what you mean by dates already passed.
 
Upvote 0
awoohaw said:
the formula should only count dates for the particular MSO from Today to Today+60. I'm not sure what you mean by dates already passed.
Click to expand...
I'm probably going to have to create another column to account for those dates that have already passed. If you noticed it did not count the dates before today. That's was my major problem from the beginning with this product. Since I'm trying to count dates I have not figured out how not to count the default (0-jan-00) dates.
I may be confusing you but the formula you provided does do what I asked minus the above. Thank you.
 
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