Excel Forum

How to count conditionally formatted colored cells

Nagaraj Rathinam

New Member

Members

Forum Posts: 2

Member Since:
March 5, 2022

Offline

April 7, 2022 - 10:04 pm

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

See Answer

Jessica Stewart

Northern USA

Member

Members

Trusted Members

Forum Posts: 219

Member Since:
February 13, 2021

Offline

April 8, 2022 - 12:57 am

Unfortunately, there isn't a way without VBA.

Riny van Eekelen

Örnsköldsvik, Sweden

Moderator

Members

Moderators

Forum Posts: 1020

Member Since:
January 31, 2022

Online

April 8, 2022 - 4:51 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

Anders Sehlstedt

Eskilstuna, Sweden

VIP

Members

Trusted Members

Forum Posts: 918

Member Since:
December 7, 2016

Offline

April 8, 2022 - 5:35 am

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