frenchshipp
New Member
- Joined
- May 17, 2016
- Messages
- 1
Good day,
I create status dashboards for leadership and use the typical red (late), yellow (at risk), green (on target), blue (complete) color coding.
I have been trying to work a formula that will count the # of each color based on 2 other criteria, organization function and Go-Live week (both on another sheet within the same workbook) to create "index card" views of each weeks' activity.
The countccolor function works well on it's own, but isn't playing well with the COUNTIFS criteria. I need to figure out how to incorporate them all together so they return 1 count in a defined cell.
I've used the formula =COUNTIFS('Dashboard Details'!A:A,'Week by OR GYR'!B6,'Dashboard Details'!L:L,'Week by OR GYR'!O2,'Dashboard Details'!K:K,countccolor('Dashboard Details'!K:K,I6)), which did not return the correct result. On a separate attempt, I added backolor to the name manager in columns to the far right (out of the dashboard view) to get 3 for red, 6 for yellow, 10 for green and 33 for blue. Using this formula doesn't seem to work either =COUNTIFS(AND("Dashboard Details'!k:k,backcolor=10)'Dashboard Details'!A:A,"LCSA",'Dashboard Details'!L:L,"5/20/2016"))
Below is an example of an index card for the Go Live week of May 20th, right now, I'm manually counting the project statuses on the Dashboard Detail tab and entering them in these. I'd like to be able to automate the count on the index cards from the Dashboard Detail data table.
Any guidance would be greatly appreciated!!
I create status dashboards for leadership and use the typical red (late), yellow (at risk), green (on target), blue (complete) color coding.
I have been trying to work a formula that will count the # of each color based on 2 other criteria, organization function and Go-Live week (both on another sheet within the same workbook) to create "index card" views of each weeks' activity.
The countccolor function works well on it's own, but isn't playing well with the COUNTIFS criteria. I need to figure out how to incorporate them all together so they return 1 count in a defined cell.
I've used the formula =COUNTIFS('Dashboard Details'!A:A,'Week by OR GYR'!B6,'Dashboard Details'!L:L,'Week by OR GYR'!O2,'Dashboard Details'!K:K,countccolor('Dashboard Details'!K:K,I6)), which did not return the correct result. On a separate attempt, I added backolor to the name manager in columns to the far right (out of the dashboard view) to get 3 for red, 6 for yellow, 10 for green and 33 for blue. Using this formula doesn't seem to work either =COUNTIFS(AND("Dashboard Details'!k:k,backcolor=10)'Dashboard Details'!A:A,"LCSA",'Dashboard Details'!L:L,"5/20/2016"))
Below is an example of an index card for the Go Live week of May 20th, right now, I'm manually counting the project statuses on the Dashboard Detail tab and entering them in these. I'd like to be able to automate the count on the index cards from the Dashboard Detail data table.
Any guidance would be greatly appreciated!!
| Excel 2012 | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
| 1 | |||||||||||||||||
| 2 | Event 14 | SIT | UAT | C/O | 5/20/2016 | ||||||||||||
| 3 | OR Function | ||||||||||||||||
| 4 | Finance | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||
| 5 | LCSA | 0 | 0 | 0 | 2 | 0 | 0 | 0 | 2 | 20 | 0 | 0 | 0 | 24 | |||
| 6 | Facilities Management | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 3 | |||
| 7 | Communications and Change | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 2 | 0 | 0 | 0 | 4 | |||
| 8 | IT Operations | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 2 | 0 | 0 | 0 | 4 | |||
| 9 | Total | 1 | 0 | 0 | 3 | 0 | 0 | 3 | 3 | 24 | 0 | 0 | 1 | 35 | |||
| 10 | |||||||||||||||||
Week by OR GYR | |||||||||||||||||
