Help required with COUNTIFS

Currymonster30

New member
Joined
Sep 6, 2016
Messages
18
Reaction score
0
Points
0
Excel Version(s)
2010
Hi, I wonder if somebody could assist.
I’ve been trying to resolve something but seem to keep going round in circles.
I have a simple table on the ‘ACTION’ tab of the workbook that has teachers assigned to managers who audit their work on a quarterly basis and then any identified actions need to be completed by a certain date.
On the ‘OUTPUTS’ tab is where I’m having the issue where I have these four tables that I want to reference data on the ‘Action’ Tab.

  • Initial Actions Head of Dept
  • Review Actions Head of Dept
  • Initial Actions Teacher
  • Review Actions Teacher
What I’m trying to do is for the Initial Actions Head of Dept Table is look up column H on the Action Tab to see who the supervising manager is and then see how many initial actions have been identified in Colum J on the ‘ACTION’ Tab and then split it by head of Dept and Month and the chart the data split by manager and month for each table.
I’m trying to do exactly the same thing for the Review Actions Head of Dept where the head of Dept would be still looked up in Colum H of the ‘Action’ tab but this time the ‘Review Audit Date’ in Colum L of the ‘Action’ Tab would be referenced.
I’d also like the same thing for the two teacher tables this time referencing Colum I ‘Teacher’ on the ‘ACTION’ tab and then Colum J ‘Initial Actions Due By’ split by both teacher name and month for the Initial Actions Teacher and reference Colum I ‘Teacher’ on the ‘ACTION’ tab and this time Colum L ‘Review Audit Date’ again split by both teacher name and month for the Review Actions Teacher
I’ve been trying to use COUNTSIFS to do and have managed to reference the manager and also calculate if the date is within a certain month, I’m referencing ‘Month Dates’ on the ‘OUTPUTS’ tab at S2—T13 this but can’t seem to get the formula to work with an additional criteria to spilt it by manager and teacher for the rest of the tables.
This is as far as I’ve got with the formula, which works so far but I can’t seem to add a criteria which makes it spilt by manager=COUNTIFS(ACTION!$G$12:$G$39,">="&S3,ACTION!$G$12:$G$39,"<="&T3)
Can anybody help?
 

Attachments

  • TestFollow up actions.xlsx
    35.4 KB · Views: 13
See Sheet3 of the attached and tell me if those are the results you're looking for.
 

Attachments

  • ExcelGuru8886TestFollow up actions.xlsx
    48.8 KB · Views: 14
Thanks loads P45cal, its just what I'm after feel like a bit of a fool for not considering pivots!
 
Back
Top