another excel formula question
Results 1 to 6 of 6

Thread: another excel formula question

  1. #1
    Senior Member
    Join Date
    Sep 2005
    Posts
    332

    Question 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

  2. #2
    Senior Member
    Join Date
    Jul 2002
    Posts
    339
    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


  3. #3
    Senior Member
    Join Date
    Sep 2005
    Posts
    332
    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

  4. #4
    Senior Member ShippMA's Avatar
    Join Date
    Oct 2002
    Posts
    165
    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
    www.simpleits.co.uk
    www.tazforum.**********.com
    Google is god ....... of the Internet

  5. #5
    Senior Member
    Join Date
    Nov 2005
    Posts
    115

    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. #6
    Senior Member
    Join Date
    Sep 2005
    Posts
    332
    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
  •