The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

How can I use the CountIF, for a range of values i.e. between 60 - 90 days, between 31 and 59 days, and less than 30?

Network secuirty disables macros, but VB works.

Network secuirty disables macros, but VB works.

=countifs(A:A,">=60",A:A,"

unles you're dealing with dates, in which case you could have

=countifs(A:A,"<=" & today()-60 ,A:A,">" & today()-90)

or, depending on whether it's 60 days in the future or in the past:

=countifs(A:A,">=" & today()+60 ,A:A,"<" & today()+90)

Thomas

So, yes I have multiple columns and want to find the difference in dates and then count? The Expiration date

SO, I basically need to calculate the following:

The number of days until Expiration in a colum (K14), countif based upon criteria in K14

1. 30 Days or less

2. Between 31 to 59 days

3.Between 60 and 90 days

4. Between 91 and 179

5. 180 days or more

=SUMPRODUCT(--(K14-$A$2:$A

=SUMPRODUCT(--(K14-$A$2:$A

=SUMPRODUCT(--(K14-$A$2:$A

=SUMPRODUCT(--(K14-$A$2:$A

=SUMPRODUCT(--(K14-$A$2:$A

adjust ranges to suit, but don't use large (whole) columsn.

125MXG-CSS-Personnel-Tracker-v1..xlsm

If you use column F or any other column of dates, then you get numeric result:

e.g. =SUMPRODUCT(--(M5-$F$14:$F

So, what I actually need is to do is if the the following:

1. If current date is outside the 0-365 day range of ETS, say:6 Mo of ETS (Expiration Date), but NLT 90 Days of ETS

2. If the current dates is within the 0-365 day range of the ETS(Expiration Date), then countdown and say or show days: ## Days

3. If the expiration dates has passed, then say: Please validate ETS status of member

=IF(B14>(B14-180),"6 Mo of ETS, but NLT 90 Days of ETS",IF(TODAY()<= B14-180,TODAY()-B14&" Days Remaining to ETS",""))

=IF(B14-$M$5<=180,IF(OR(B1

copied down.

I miss spoke or type, if the ETS (Expiration date) is out that 0-365 day range, then , say: 6 Mo of ETS (Expiration Date), but NLT 90 Days of ETS

2.But, If the expiration date is within the 0-365 day range of the ETS(Expiration Date), then countdown and say or show days: ##

3. If the expiration dates has passed, then say: Please validate ETS status of member

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Other than that, the formula for 6 months (~180 days) is:

=IF(B14-$M$5<=180,IF(OR(B1

If you really mean 365 days, then:

=IF(B14-$M$5<=365,IF(OR(B1