Countif multiple conditions -I can't work it out

[kristell]

Hi - I have a spreadsheet which is quite large, it contains the names of all heads of service together with their website pages which are due to be reviewed to ensure content is accurate and up to date at certain times. I have to produce another report each month to say which heads of service have reviewed their pages or haven't etc., along these lines :


head of service 1 pages not reviewed | pages to be reviewed next month | pages not due for review
head of service 2 pages not reviewed | pages to be reviewed next month | pages not due for review
head of service 3 pages not reviewed | pages to be reviewed next month | pages not due for review

etc.,


The names of the heads of service are in column O - about 600 different rows (web pages) with about 30 different heads of service

I have three columns

Column J Date due for next review THIS COLUMN IS =K2+L2
Column K Date last reviewed THIS IS THE DATE THE REPORT WAS LAST REVIEWED
Column L Review frequency (days) THIS COLUMN CONTAINS NUMBER OF DAYS TILL HEAD OF SERVICE NEXT WANTS TO REVIEW THE WEB PAGE

Column J then has conditional formatting:

Overdue for review = less than or equal to today RED FILL (I HAVE TODAYS DATE IN A SEPARATE CELL (p1)
Needs review = Less than or equal to today + 30 AMBER FILL
No review needed = greater than or equal to today + 31 GREEN FILL


So what I would like is on the same worksheet but outside of the print range the info:

head of service 1 pages not reviewed | pages to be reviewed next month | pages not due for review
head of service 2 pages not reviewed | pages to be reviewed next month | pages not due for review
head of service 3 pages not reviewed | pages to be reviewed next month | pages not due for review


Not sure what is the best way to do this - any help would be greatfully received.

This is as far as I have got: =COUNTIF(O2:O513,"head of service name") in the three columns but I need to also tell me how may of his pages have not been reviewed, due to be reviewed next month and how many not yet due for review!!

Many thanks

 

[kristell]

Just wondered if I am making this too complicated. I have added a column which is effectively 'days overdue'.... Can I do a countif 'specific text' and overdue certain amount of days that matches:

Overdue for review = less than or equal to today RED FILL (I HAVE TODAYS DATE IN A SEPARATE CELL (p1)
Needs review = Less than or equal to today + 30 AMBER FILL
No review needed = greater than or equal to today + 31 GREEN FILL

 

[Andrew Poulsom]

Welcome to MrExcel.

For multiple conditions you need COUNTIFS. Check it out in Excel Help.

 

[kristell]

I have tried many different ways of doing this but just can not get it to work - using Countif using countifs - that is why I wondered if someone can help with my specific problem - as I can not get my head round it - I have spent seven hours googling for different ways and still not found anything that helps me. I am sorry if this is not the correct place to ask for help.

 

[Andrew Poulsom]

Is it eg?

=COUNTIF(O2:O513,"head of service name",J2:J513,"<="&TODAY())

 

[kristell]

I have just tried it on a test sheet using a column with days overdue and got this which has seemed to work - finally!:

Red Amber Green Total
Nick =COUNTIFS(I21:I39,">1",G21:G39,"Nick") =COUNTIFS(I21:I39,"<1",I21:I39,">-30",G21:G39, "Nick") =COUNTIFS(I21:I39,"<-30",G21:G39,"Nick") =COUNTIF(G21:G39,"Nick")



Carol =COUNTIFS(I21:I39,">0",G21:G39,"Carol") =COUNTIFS(I21:I39,"<1",I21:I39,">-32",G21:G39, "Carol") =COUNTIFS(I21:I39,"<-30",G21:G39,"Carol") =COUNTIF(G21:G39,"Carol")


If you can see any errors in the formula please let me know