+ Reply to Thread
Results 1 to 2 of 2

COUNTIFS to calculate late date by 4 months

  1. #1
    Registered User
    Join Date
    03-10-2014
    Location
    Naperville, IL
    MS-Off Ver
    Excel 2010
    Posts
    13

    COUNTIFS to calculate late date by 4 months

    I need a formula of COUNTIFS to calculate in the column month, the number of animals that have not gotten shots after 4 months time period but exclude the ones that don't require shots.

    The formula [=COUNTIFS(NameTab1!$B:$B,'OUTPUT - Numbers'!$A23&"*",NameTab1!$C:$C,"Yes", NameTab1!$E:$E,">="&'OUTPUT - Numbers'!B$20,NameTab1!$E:$E,"<"&EDATE('OUTPUT - Numbers'!B$20,1))] in the second table, "Last Date of Shot for an Animal that Requires Shots" works for when the shot was last done but I need to minus 4 months from the column month date to get the numbers in the first table of what the overdue numbers looked like for that particular month.

    Example criteria is this for April, AET only had 12 animals that hadn't received shots more than 4 month ago and excluded the ones that don't require shots as indicated in Column C of the NameTab1. However in March, there were only 2 total cases from that month. Therefore, 10 additional cases were added to April's column.

  2. #2
    Registered User
    Join Date
    03-10-2014
    Location
    Naperville, IL
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: COUNTIFS to calculate late date by 4 months

    Hey everyone, I think I solved it with this formula =COUNTIFS(NameTab1!$B:$B,'OUTPUT - Numbers'!$A21&"*",NameTab1!$C:$C,"Yes", NameTab1!$E:$E,"<"&'OUTPUT - Numbers'!B$20,NameTab1!$E:$E,"<"&EDATE('OUTPUT - Numbers'!B$20,-3)).

    Thanks for everyone for looking at this. Talking it out with a co-worker helped out so much.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Calculate end date base don Start date + 6 months
    By Spritz in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-14-2013, 02:20 PM
  2. [SOLVED] Calculate date based on number of weeks or months after start date
    By wotsup in forum Excel General
    Replies: 3
    Last Post: 08-17-2012, 06:00 AM
  3. Auto calculate no. of months given a date range.
    By gilbert in forum Excel General
    Replies: 5
    Last Post: 07-12-2010, 03:05 AM
  4. Calculate Date Six Months from Cell Date, ongoing
    By OZover in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-17-2010, 09:13 AM
  5. How do I calculate interest and deduct late fees based on date pa.
    By leon in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 01-06-2005, 11:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1