Notifications
Clear all
General Excel Questions & Answers
4
Posts
4
Users
0
Reactions
133
Views
Topic starter
Hi, Good Day,
Kindly guide me on how to count conditionally formatted colored cells
| Sl. No | Issue Date | Target Period | Target Date | Actual Closeout Date | Workflow Status | Remarks |
| 1 | 18-Apr-19 | 30 | 18-May-19 | 10-May-19 | Closed | |
| 2 | 10-May-19 | 30 | 9-Jun-19 | 21-Jun-19 | Closed | |
| 3 | 6-Jun-19 | 30 | 6-Jul-19 | 30-Jun-19 | Closed | |
| 4 | 15-Jun-19 | 30 | 15-Jul-19 | 30-Jun-19 | Closed | |
| 5 | 12-Jul-19 | 30 | 11-Aug-19 | 8-Aug-19 | Closed | |
| 6 | 24-Feb-20 | 30 | 25-Mar-20 | 15-Mar-20 | Closed | |
| 7 | 25-Feb-20 | 30 | 26-Mar-20 | 15-Mar-20 | Closed | |
| 8 | 3-Mar-20 | 30 | 2-Apr-20 | 10-Apr-20 | Closed | |
| 9 | 8-Mar-20 | 30 | 7-Apr-20 | 6-Apr-20 | Closed | |
| 10 | 9-Mar-20 | 30 | 8-Apr-20 | 16-Apr-20 | Closed | |
| 11 | 29-April-2021 | 30 | 29-May-21 | 25-May-21 | Closed | |
| 12 | 03-May-2021 | 30 | 2-Jun-21 | 3-Jun-21 | Closed | |
| 13 | 15-May-2021 | 30 | 14-Jun-21 | 18-Jun-21 | Closed | |
| 14 | 20-May-2021 | 30 | 19-Jun-21 | 18-Jun-21 | Closed | |
| 15 | 03-June-2021 | 30 | 3-Jul-21 | 9-Jul-21 | Closed | |
| 16 | 31-Jan-22 | 30 | 2-Mar-22 | 28-Feb-22 | Closed | |
| 17 | 31-Jan-22 | 30 | 2-Mar-22 | 5-Mar-22 | Open | |
| 18 | 4-Feb-22 | 30 | 6-Mar-22 | 5-Mar-22 | Open | |
| 19 | 17-Feb-22 | 30 | 19-Mar-22 | 20-Mar-22 | Open | |
| 20 | 17-Feb-22 | 30 | 19-Mar-22 | 23-Mar-22 | Open |
| Color Code | Color | Target Status | 2019 | 2020 | 2021 | 2022 |
| On-Time | ||||||
| Overdue |
- How to count the conditionally formatted colored cells and how to summarize the same
- Excel file attached
Posted : 08/04/2022 8:04 am
Unfortunately, there isn't a way without VBA.
Posted : 08/04/2022 10:57 am
Why not use a formula similar to the CF rule for "Overdue", and its opposite for "On Time"?
In J2: =SUM(--(E2:E21<=D2:D21)), returning 11
In J3: =SUM(--(E2:E21>D2:D21)), returning 9
Posted : 08/04/2022 2:51 pm
Hello,
As Jessica writes, you need VBA if you want to count by colour, but why bother when you can use the same criterias used in the CF rules and use SUMPRODUCT.
=SUMPRODUCT(($E$2:$E$21>$D$2:$D$21)*(L$1=YEAR($E$2:$E$21)))
Br,
Anders
Posted : 08/04/2022 3:35 pm
