+ Reply to Thread
Results 1 to 9 of 9

Count basis date

  1. #1
    Registered User
    Join Date
    07-02-2020
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    4

    Question Count basis date

    Dear experts,

    I'm breaking my head over the COUNTIF formula.
    I have an excel to track the replacement of computers. I want to have a count overview of the age of the computers.

    In cell I1 I want to show the total count of all the computers younger than 3 years (< 3 years)
    In cell I2 I want to show the total count of all the computers older than 3 years but younger than 4 years (>3year s <=4 years)
    In cell I3 I want to show the total count of all the computers older than 4years.

    In column B I mention the status: active, replace, defect, decom.
    In column I is the date the computer was bought and in column J I have a formula showing the age in year and month:
    Please Login or Register  to view this content.
    If the status of the computer however is "decom", then I don't want that computer to be added to the count in I1:I3 and there' where I'm struggling.

    In cell I1 I have following formula:
    Please Login or Register  to view this content.
    In cell I2 I have following formula:
    Please Login or Register  to view this content.

    In cell I3 I have following formula:
    Please Login or Register  to view this content.
    Cell Q1 has the current date minus 3 months.
    Cell Q3 has the current date minus 4 months

    Much appreciated,

    Thanks and best regards,

    Siegfried
    aka Rvbicon
    Attached Files Attached Files
    Last edited by Rvbion; 07-02-2020 at 12:02 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Count basis date

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    07-02-2020
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    4

    Re: Count basis date

    thanks Ali, will attach
    Last edited by Rvbion; 07-02-2020 at 11:59 AM.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Count basis date

    Hi,

    In the absence of a workbook for testing try
    I1
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I2
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I3
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Count basis date

    Can you tell us what you are WANTING to see in cells I1, I2 and I3?

    Maybe:

    =COUNTIFS(I8:I72,">"&Q1,B8:B72,"<>decom")

    =COUNTIFS(I8:I72,"<"&Q1,I8:I72,">="&Q2,B8:B72,"<>decom")

    =COUNTIFS(I8:I72,"<"&Q2,B8:B72,"<>decom")

  6. #6
    Registered User
    Join Date
    07-02-2020
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    4

    Re: Count basis date

    In cell I1 I want to show the total count of all the computers younger than 3 years (< 3 years)
    In cell I2 I want to show the total count of all the computers older than 3 years but younger than 4 years (>3year s <=4 years)
    In cell I3 I want to show the total count of all the computers older than 4years.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Count basis date

    I know, but what VALUES are you expecting?

    Did you try my suggestions?

  8. #8
    Registered User
    Join Date
    07-02-2020
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    4

    Re: Count basis date

    Hi Ali,

    I just tried them and it's working, I'm getting the total count per age! Perfect!
    I was really breaking my head on this one.
    Many thanks for your help!

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Count basis date

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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. count values on the basis of complex criteria
    By nkd108 in forum Excel General
    Replies: 15
    Last Post: 11-26-2015, 05:47 AM
  2. Count all columns per row basis and = 100
    By Mach7 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-16-2012, 11:23 PM
  3. count of duplicate on month basis
    By excel5111987 in forum Excel General
    Replies: 1
    Last Post: 09-02-2011, 07:21 AM
  4. Day Count Basis in Excel
    By RICH09 in forum Excel General
    Replies: 4
    Last Post: 06-07-2011, 04:33 AM
  5. Replies: 2
    Last Post: 06-23-2009, 11:16 AM
  6. Day Count basis change
    By sabre in forum Excel General
    Replies: 6
    Last Post: 09-18-2008, 02:32 AM
  7. [SOLVED] Need to get a count on an hourly basis
    By Arun2902 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-06-2006, 05:30 PM

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