-
April 4th, 2006, 01:28 AM
#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,">(TODAY()+7)") - COUNTIF(same range,">(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.
\"He who shall introduce into public affairs the principles of primitive Christianity will change the face of the world.\"
Benjamin Franklin
-
April 4th, 2006, 01:35 PM
#2
AFAIK, the criteria in COUNTIF function only supports number and text. That is, criteria like "> today()" and "> 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 > TODAY()+7, G2 < TODAY()+21)
...
= AND(G116 > TODAY()+7, G116 < 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,
<jdenny>
Always listen to experts. They\'ll tell you what can\'t be done and why. Then go and do it. -- Robert Heinlein
I\'m basically a very lazy person who likes to get credit for things other people actually do. -- Linus Torvalds
-
April 4th, 2006, 04:25 PM
#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!
\"He who shall introduce into public affairs the principles of primitive Christianity will change the face of the world.\"
Benjamin Franklin
-
April 4th, 2006, 04:55 PM
#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
-
April 5th, 2006, 12:48 AM
#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?
-
April 7th, 2006, 12:41 AM
#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.
\"He who shall introduce into public affairs the principles of primitive Christianity will change the face of the world.\"
Benjamin Franklin
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|