# Thread: another excel formula question

1. ## another excel formula question

OK so here is the scenario:

I have a whole bunch of expiration dates in a range. I am trying to set up a formula to count the number of those expiration dates that fall between a week from today and 3 weeks from today. Here is the formula i came up with, but its returning the wrong answer.

=COUNTIF('01 Chicago'!G2:H116,"&gt;(TODAY()+7)") - COUNTIF(same range,"&gt;(TODAY()+21)")

i am counting the number of dates that will expire anytime after a week from today. then subtracting the number of dates that expire anytime after three weeks from today leaving me with the number of dates that are expiring between one week and three weeks from today.

If there is a way to set up multiple criteria in a countif statement i guess that would work too but i don't know how.

2. AFAIK, the criteria in COUNTIF function only supports number and text. That is, criteria like "&gt; today()" and "&gt; 4/4/2006" are not supported (not rejected, but simply return wrong answer).

Assumption:
The range containing expiration dates is G2:G116

First, add a (hidden) K2:K116 range with these series of formula
= AND(G2 &gt; TODAY()+7, G2 &lt; TODAY()+21)
...
= AND(G116 &gt; TODAY()+7, G116 &lt; TODAY()+21)
which returns TRUE or FALSE.

Now you can set up a cell with this formula
= COUNTIF(K2:K116, TRUE)

Hope you get the idea.

Peace always,
&lt;jdenny&gt;

3. Ah. I knew it had to be something with the TODAY statement in there but i didn't know it wasn't supported in the countif function, but it definatly makes sense sinc ei kept getting really wrong numbers returned. I had thought of something like what you suggested but was hopin to condense the count into one cell instead of useing hidden cells but i guess i can't get around it. Thanks for your help!

4. Hi,

or, if you don't want to create a new column, try this:

=SUM(COUNTIF(A1:A11,TODAY()+7),COUNTIF(A1:A11,TODAY()+8),COUNTIF(A1:A11,TODAY()+9))
(obviously put whatever range you use in)

The draw back with this is that you would need to extend it all the way to +28.

Possibly to long for the specific use you have in mind, but worth bearing in mind for the future. The biggest one of these i have ever used had 15 countif's in. They are actually really easy to construct as all you have to do is copy and paste ",COUNTIF(A1:A11,TODAY()+8)" however many times you want and then just work back through changing the number on the end.

Like i said, the main advanteage here is that you don't have to have any hidden columns

Later

Matt

5. ## Re: another excel formula question

Originally posted here by JewishIntent
I have a whole bunch of expiration dates in a range.
Isn't a bit of overkill to keep a spreadsheet of your pantry?

6. Isn't a bit of overkill to keep a spreadsheet of your pantry?
LOL this is for work. I am in charge of insurance compliancy for my copmany's nation wide installer base, and we currently use an access data base. the person who wrote it however is a complete moron and it really doesn't work too well at all so i decided to create various self updating spreadsheets to make my job a lot easier.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

×