+ Reply to Thread
Results 1 to 6 of 6

COUNTIFS with date between formula

  1. #1
    Registered User
    Join Date
    04-22-2020
    Location
    England
    MS-Off Ver
    365
    Posts
    39

    COUNTIFS with date between formula

    ---------------------
    Attached Files Attached Files
    Last edited by TaylorGC; 10-20-2020 at 09:54 AM.

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: COUNTIFS with date between formula

    Please try at B3

    =COUNTIFS(Table1[Action raised (date)],">="&LEFT(LOOKUP(COLUMN(B1:G1),COLUMN(B1:G1)/(B1:G1>0),B1:G1),10),Table1[Action raised (date)],"<="&RIGHT(LOOKUP(COLUMN(B1:G1),COLUMN(B1:G1)/(B1:G1>0),B1:G1),10),Table1[Priority/Risk],B2:G2&"*",Table1[Action Source],$A3:$A5)
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    04-22-2020
    Location
    England
    MS-Off Ver
    365
    Posts
    39

    Re: COUNTIFS with date between formula

    ----------------------
    Last edited by TaylorGC; 10-20-2020 at 09:54 AM.

  4. #4
    Registered User
    Join Date
    04-22-2020
    Location
    England
    MS-Off Ver
    365
    Posts
    39

    Re: COUNTIFS with date between formula

    Hi I was hoping somebody could help with this one, I did get somewhere yesterday however the formula didn't work as it needed it too

    I have 2 columns with data that I need it to countifs and then count if it falls between 2 dates that I have in a validation tab it works in periods so period 1 runs from 26/9/20 to 23/10/20

    I need the formula to tell me how many actions were raised (date created column) in that period and how many actions were closed (date completed) in the period the data fields as you'll see on the spreadsheet are whether its and high intermediate or low action and if it's one of 3 other fields PIP MH, PIP WPT or PIP RR

    essentially I'm asking the sheet to tell me if an action was created on the 10/10/20 and it was high and it was a PIP MH then in the High/MH cell for open it would count 1 and if that action had been closed out in that same period it would also count 1 but in the closed cell for that period

    hope that makes sense, I've put some info onto the sheet which hopefully is better than what I produced yesterday
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-22-2020
    Location
    England
    MS-Off Ver
    365
    Posts
    39

    Re: COUNTIFS with date between formula

    Please Help!

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: COUNTIFS with date between formula

    It looks as if there were a lot of changes made to the layout of the workbook since Bo_Ry gave a very good solution to the original question.
    In the attached copy of the file attached to post #4 some changes have been made.
    The M's in row 32 of the Stats sheet have been changed to I's because the Validation sheet uses the list High, Intermediate and Low so there is no M[edium].
    Column M on the Validation sheet is changed to read Period 1 etc. to match the text on the Stats sheet
    A column (T) has been added to table 1 to display the period as actual dates are no longer used as they were in row 1 of the PIP Data sheet of the original file.
    The column is populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In A33:A38 on the Stats sheet the word Closed is changed to Complete to match the Status column in table 1.
    The formula used to populate B33:G38 on the Stats sheet is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Countifs with date range formula Broke
    By rogrand in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-09-2020, 02:56 PM
  2. [SOLVED] COUNTIFS formula between 2 date ranges Challenge
    By rz6657 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-28-2019, 02:57 PM
  3. [SOLVED] Please help!!! Countifs formula for the columns contains date today
    By kylechace in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2017, 05:05 AM
  4. COUNTIFS formula for reading date/time stamps
    By Trian3 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-31-2014, 08:20 AM
  5. Using a COUNTIFS formula but need once of critierias to be a date...??
    By toddy1980 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-30-2012, 08:05 AM
  6. Inserting countifs formula using vba.....having problem with date
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-26-2011, 05:54 PM
  7. Using Date Range in Countifs formula
    By Kenny_D in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-09-2010, 10:57 AM

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