+ Reply to Thread
Results 1 to 9 of 9

Countif multiple criteria and then check the result against a date (ageing)

  1. #1
    Registered User
    Join Date
    07-31-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    34

    Countif multiple criteria and then check the result against a date (ageing)

    Hi there,

    I have 6 columns, lets call then A,B,C,D,E,F. The first 3 columns pertain to a fixed dropdown box cell and the remaining 3 cells in each row pertain another fixed cell.

    I am using COUNTIFS to count whether a column meets a certain criteria based on the entries in each row i.e equals Open and London respectively in columns E and F respectively.

    If it does then I want check this result against a date (Column G) to tell me if it has been OPEN for <30, 30, 60, 90 or 120 days.......this is the part I am having issues with. How do I add this to the whole COUNTIFS formula after it's done the initial check?

    Can anybody help?

    The formula I am using is:

    =COUNTIFS("A:A","=Position","B;B","=Area","C:C,"="&FIXED CELL","D:D","=OPEN","E:E","=London","F:F","="&FIXED CELL)

  2. #2
    Registered User
    Join Date
    10-25-2012
    Location
    California, USA
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: Countif multiple criteria and then check the result against a date (ageing)

    Can you post a sample file with example data?

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Countif multiple criteria and then check the result against a date (ageing)

    Hi and welcome to the forum

    If I understand you correctly, you need to remove the = in the criteria, the "" from the range and for some ranges, you use : and for others you use ; check to see which 1 you need and stick with that...

    =COUNTIFS(A:A,"Position",B:B,"Area",C:C,"FIXED CELL",D:D,"OPEN",E:E,"London",F:F,"FIXED CELL")

    Im not sure if "FIXED CELL" is text you are looking for, or if you mean a specific cell reference? If you mean a cell ref, then use this - change reference as needed...

    =COUNTIFS(A:A,"Position",B:B,"Area",C:C,$A$1,D:D,"OPEN",E:E,"London",F:F,$A$2)

    This assumes that you want to count based on...
    Position being in A
    Area being in B
    A specific entry being in C
    Open being in D
    London being in E
    A specific entry being in F

    If you still have a problem, i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    07-31-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    34

    Re: Countif multiple criteria and then check the result against a date (ageing)

    Sorry maybe I wasn't clear in my first statement. The "" are simply to indicate a column taken from another worksheet and are meant as markers. The formula I use works perfectly. What isn't working, or what I can't figure out, is how I can count / measure the result from an ageing perspective. So if my countifs calculates 10 results out of 100 then I need to determine how the ageing of those results. So count of 10 yet only 3 are within 30 days from today.

    Make sense?

    Thank you in advance.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Countif multiple criteria and then check the result against a date (ageing)

    Nope, sorry, doesnt make sense

    If you still have a problem, i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.

  6. #6
    Registered User
    Join Date
    10-25-2012
    Location
    California, USA
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: Countif multiple criteria and then check the result against a date (ageing)

    It sounds like you will need another countifs statement. This one will have to include a calculation of today's date - column g. and if it is greater than or less than your appropriate dates.

  7. #7
    Registered User
    Join Date
    07-31-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    34

    Re: Countif multiple criteria and then check the result against a date (ageing)

    Hi there,

    here is an example of what I am trying to achieve. My thoughts are to "add" the date formula to the end of the COUNTIFS statement, however, I just cannot seem to get it right or am probably trying to do something that is near impossible??

    Any help would be appreciated.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    10-25-2012
    Location
    California, USA
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: Countif multiple criteria and then check the result against a date (ageing)

    Ok, it took me awhile to work out a solution. I have placed it into the second sheet of the workbook. The date values did not match your expectations, but please look at the actual #days column for the date difference.

    Book2(solution).xlsx

  9. #9
    Registered User
    Join Date
    07-31-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    34

    Re: Countif multiple criteria and then check the result against a date (ageing)

    Spot on chap....works a charm.

    Now I believe, from previous visits to the site, that I need to close this post off, etc. Not too sure how this is done so any guidance would be much appreciated.

    Once again thank you.

+ 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] COUNTIF Criteria Does Not Return Expected Result
    By SLJackson in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-30-2013, 04:12 PM
  2. [SOLVED] COUNTIF / SUMPRODUCT- multiple criteria - including date range
    By Sph01 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-31-2012, 04:54 AM
  3. COUNTIF for multiple criteria with date range
    By pjlau in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-04-2012, 07:04 AM
  4. [SOLVED] Countif and date check for second criteria
    By [email protected] in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-29-2005, 05:45 PM
  5. [SOLVED] Help Please- How to offset Date to correct column for Ageing
    By Roger Swinderman in forum Excel General
    Replies: 2
    Last Post: 09-15-2005, 06:05 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