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

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
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)))
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
Did you verify that the formula I gave you counts and does not sum?
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
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.
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

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