Forum

Notifications
Clear all

How to count conditionally formatted colored cells

4 Posts
4 Users
0 Reactions
100 Views
(@nagaraj_01443)
Posts: 2
Active Member
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        
  1. How to count the conditionally formatted colored cells and how to summarize the same
  2. Excel file attached
 
Posted : 08/04/2022 8:04 am
(@jstewart)
Posts: 216
Estimable Member
 

Unfortunately, there isn't a way without VBA.

 
Posted : 08/04/2022 10:57 am
Riny van Eekelen
(@riny)
Posts: 1217
Member Moderator
 

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
Anders Sehlstedt
(@sehlsan)
Posts: 974
Prominent Member
 

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
Share: