---------------------
---------------------
Last edited by TaylorGC; 10-20-2020 at 09:54 AM.
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)
----------------------
Last edited by TaylorGC; 10-20-2020 at 09:54 AM.
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
Please Help!
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: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:Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks