Count conditional formatting color

T

tcb92

New Member
Joined
May 22, 2018
Messages
1
Hi all,
thanks for your help in advance, I've been struggling to find something that fits my need. I have a table of KPI measurements for a number of people/ locations within tables that are conditionally formatted based upon the criteria.
I want to be able to count how many people/locations have met the kpi based on counting the color. Some of the conditional formatted measurements are values, some are text, some are blank, some are percentage measures - hence my asking for color.
I have 6 tables on 6 tabs, ranging in size. The countcellsbycolor macros that are about haven't worked because of the conditional formatting, but the principle of it is what I would like.

Summary of data below - each column has variety of colours:

[TABLE="width: 1942"]
<tbody>[TR]
[TD]95%
[/TD]
[TD]95%
[/TD]
[TD]2
[/TD]
[TD]n
[/TD]
[TD]n/a
[/TD]
[TD]n/a
[/TD]
[TD][/TD]
[TD]n/a
[/TD]
[TD] 5/5
[/TD]
[TD][/TD]
[TD] 5/5
[/TD]
[TD][/TD]
[TD][/TD]
[TD]47
[/TD]
[TD]215%
[/TD]
[TD]1,468
[/TD]
[TD]1.90%
[/TD]
[TD]0.87%
[/TD]
[TD]£10
[/TD]
[TD][/TD]
[TD]-£ 5,606
[/TD]
[TD]319
[/TD]
[TD] £ 2,093
[/TD]
[TD]-£ 153
[/TD]
[TD]-£ 1,304
[/TD]
[TD] £ 276
[/TD]
[/TR]
[TR]
[TD]55%
[/TD]
[TD]55%
[/TD]
[TD]-9
[/TD]
[TD]y
[/TD]
[TD]n/a
[/TD]
[TD]n/a
[/TD]
[TD][/TD]
[TD]2 (0)
[/TD]
[TD] 5/5
[/TD]
[TD][/TD]
[TD] 5/5
[/TD]
[TD][/TD]
[TD][/TD]
[TD]39
[/TD]
[TD]160%
[/TD]
[TD]2,539
[/TD]
[TD]8.00%
[/TD]
[TD]7.48%
[/TD]
[TD]£50
[/TD]
[TD][/TD]
[TD]-£ 15,548
[/TD]
[TD]27
[/TD]
[TD] £ -
[/TD]
[TD]-£ 352
[/TD]
[TD] £ 5,661
[/TD]
[TD]-£ 925
[/TD]
[/TR]
[TR]
[TD]72%
[/TD]
[TD]72%
[/TD]
[TD]-15
[/TD]
[TD]n
[/TD]
[TD]100%
[/TD]
[TD]100%
[/TD]
[TD][/TD]
[TD]2 (0)
[/TD]
[TD] 5/5
[/TD]
[TD][/TD]
[TD] 5/5
[/TD]
[TD][/TD]
[TD][/TD]
[TD]-17
[/TD]
[TD]-62%
[/TD]
[TD][/TD]
[TD]2.10%
[/TD]
[TD]3.85%
[/TD]
[TD]£93,122
[/TD]
[TD][/TD]
[TD]-£ 5,023
[/TD]
[TD]36
[/TD]
[TD] £ -
[/TD]
[TD] £ 408
[/TD]
[TD] £ 811
[/TD]
[TD] £ 71
[/TD]
[/TR]
[TR]
[TD]91%
[/TD]
[TD]91%
[/TD]
[TD]-13
[/TD]
[TD]y
[/TD]
[TD]0%
[/TD]
[TD]100%
[/TD]
[TD][/TD]
[TD]1 (1)
[/TD]
[TD] 1/5 4/5
[/TD]
[TD][/TD]
[TD] 1/5 4/5
[/TD]
[TD][/TD]
[TD][/TD]
[TD]87
[/TD]
[TD]414%
[/TD]
[TD]1,557
[/TD]
[TD]17.40%
[/TD]
[TD]2.99%
[/TD]
[TD]£22,861
[/TD]
[TD][/TD]
[TD]-£ 869
[/TD]
[TD]-220
[/TD]
[TD] £ 40
[/TD]
[TD]-£ 43
[/TD]
[TD] £ 82
[/TD]
[TD] £ -
[/TD]
[/TR]
[TR]
[TD]68%
[/TD]
[TD]68%
[/TD]
[TD]-6
[/TD]
[TD]n
[/TD]
[TD]100%
[/TD]
[TD]100%
[/TD]
[TD][/TD]
[TD]1 (1)
[/TD]
[TD] 5/5
[/TD]
[TD][/TD]
[TD] 5/5
[/TD]
[TD][/TD]
[TD][/TD]
[TD]-11
[/TD]
[TD]-18%
[/TD]
[TD]1,199
[/TD]
[TD]0.00%
[/TD]
[TD]3.12%
[/TD]
[TD]£85,033
[/TD]
[TD][/TD]
[TD]-£ 15,555
[/TD]
[TD]349
[/TD]
[TD] £ 333
[/TD]
[TD]-£ 657
[/TD]
[TD]-£ 12,648
[/TD]
[TD] £ 519
[/TD]
[/TR]
[TR]
[TD]75%
[/TD]
[TD]75%
[/TD]
[TD]-3
[/TD]
[TD]n
[/TD]
[TD]n/a
[/TD]
[TD]n/a
[/TD]
[TD][/TD]
[TD]2 (0)
[/TD]
[TD]n/a
[/TD]
[TD]n/a
[/TD]
[TD]n/a
[/TD]
[TD][/TD]
[TD][/TD]
[TD]56
[/TD]
[TD]105%
[/TD]
[TD][/TD]
[TD]0.00%
[/TD]
[TD]-0.66%
[/TD]
[TD]-£11,554
[/TD]
[TD][/TD]
[TD] £ 8,104
[/TD]
[TD]-82
[/TD]
[TD] £ -
[/TD]
[TD] £ 120
[/TD]
[TD]-£ 5,603
[/TD]
[TD] £ 852
[/TD]
[/TR]
[TR]
[TD]86%
[/TD]
[TD]86%
[/TD]
[TD]-29
[/TD]
[TD]y
[/TD]
[TD]33%
[/TD]
[TD]67%
[/TD]
[TD][/TD]
[TD]2 (1)
[/TD]
[TD] 2/5 3/5
[/TD]
[TD][/TD]
[TD] 2/5 3/5
[/TD]
[TD][/TD]
[TD][/TD]
[TD]38
[/TD]
[TD]60%
[/TD]
[TD]18,009
[/TD]
[TD]3.50%
[/TD]
[TD]0.54%
[/TD]
[TD]£16,569
[/TD]
[TD][/TD]
[TD]-£ 8,013
[/TD]
[TD]-138
[/TD]
[TD] £ 81
[/TD]
[TD]-£ 1,030
[/TD]
[TD]-£ 202
[/TD]
[TD] £ 174
[/TD]
[/TR]
[TR]
[TD]80%
[/TD]
[TD]80%
[/TD]
[TD]-15
[/TD]
[TD]y
[/TD]
[TD]n/a
[/TD]
[TD]n/a
[/TD]
[TD][/TD]
[TD]n/a
[/TD]
[TD] 5/5
[/TD]
[TD][/TD]
[TD] 5/5
[/TD]
[TD][/TD]
[TD][/TD]
[TD]-24
[/TD]
[TD]-349%
[/TD]
[TD]99
[/TD]
[TD]2.30%
[/TD]
[TD]4.46%
[/TD]
[TD]£135,333
[/TD]
[TD][/TD]
[TD]-£ 6,728
[/TD]
[TD]-158
[/TD]
[TD] £ 65
[/TD]
[TD] £ 551
[/TD]
[TD]-£ 3,829
[/TD]
[TD]-£ 513
[/TD]
[/TR]
[TR]
[TD]94%
[/TD]
[TD]94%
[/TD]
[TD]-3
[/TD]
[TD]n
[/TD]
[TD]0%
[/TD]
[TD]67%
[/TD]
[TD][/TD]
[TD]n/a
[/TD]
[TD] 4/5
[/TD]
[TD]E
[/TD]
[TD] 4/5
[/TD]
[TD][/TD]
[TD][/TD]
[TD]-8
[/TD]
[TD]-23%
[/TD]
[TD]2,176
[/TD]
[TD]2.20%
[/TD]
[TD]5.69%
[/TD]
[TD]£123,844
[/TD]
[TD][/TD]
[TD]-£ 10,972
[/TD]
[TD]-57
[/TD]
[TD]-£ 5,650
[/TD]
[TD]-£ 1,121
[/TD]
[TD]-£ 6,949
[/TD]
[TD]-£ 386
[/TD]
[/TR]
[TR]
[TD]71%
[/TD]
[TD]71%
[/TD]
[TD]-13
[/TD]
[TD]y
[/TD]
[TD]n/a
[/TD]
[TD]n/a
[/TD]
[TD][/TD]
[TD]n/a
[/TD]
[TD] 5/5
[/TD]
[TD][/TD]
[TD] 5/5
[/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD]1129%
[/TD]
[TD][/TD]
[TD]2.50%
[/TD]
[TD]10.94%
[/TD]
[TD]£77,355
[/TD]
[TD][/TD]
[TD]-£ 5,967
[/TD]
[TD]27
[/TD]
[TD] £ 250
[/TD]
[TD]-£ 229
[/TD]
[TD] £ 344
[/TD]
[TD] £ -
[/TD]
[/TR]
[TR]
[TD]20%
[/TD]
[TD]20%
[/TD]
[TD]-42
[/TD]
[TD]y
[/TD]
[TD]n/a
[/TD]
[TD]n/a
[/TD]
[TD][/TD]
[TD]n/a
[/TD]
[TD] 5/5
[/TD]
[TD][/TD]
[TD] 5/5
[/TD]
[TD][/TD]
[TD][/TD]
[TD]160
[/TD]
[TD]187%
[/TD]
[TD]5,604
[/TD]
[TD]0.00%
[/TD]
[TD]5.65%
[/TD]
[TD]£160,430
[/TD]
[TD][/TD]
[TD]-£ 1,189
[/TD]
[TD]-81
[/TD]
[TD]-£ 45
[/TD]
[TD]-£ 892
[/TD]
[TD] £ 39
[/TD]
[TD] £ 140
[/TD]
[/TR]
[TR]
[TD]80%
[/TD]
[TD]80%
[/TD]
[TD]-17
[/TD]
[TD]n
[/TD]
[TD]50%
[/TD]
[TD]50%
[/TD]
[TD][/TD]
[TD]1 (0)
[/TD]
[TD] 5/5
[/TD]
[TD][/TD]
[TD] 5/5
[/TD]
[TD][/TD]
[TD][/TD]
[TD]-8
[/TD]
[TD]-183%
[/TD]
[TD]315
[/TD]
[TD]5.00%
[/TD]
[TD]4.46%
[/TD]
[TD]£28,354
[/TD]
[TD][/TD]
[TD]-£ 1,906
[/TD]
[TD]-282
[/TD]
[TD]-£ 38
[/TD]
[TD]-£ 215
[/TD]
[TD]-£ 2,139
[/TD]
[TD] £ -
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]90%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]50%
[/TD]
[TD]50%
[/TD]
[TD]8%
[/TD]
[TD]50%
[/TD]
[TD]33%
[/TD]
[TD]50%
[/TD]
[TD]100%
[/TD]
[TD][/TD]
[TD]100%
[/TD]
[TD]91%
[/TD]
[TD]90%
[/TD]
[TD]100%
[/TD]
[TD]100%
[/TD]
[TD]58%
[/TD]
[TD]25%
[/TD]
[TD]25%
[/TD]
[TD]25%
[/TD]
[TD]50%
[/TD]
[TD]42%
[/TD]
[TD][/TD]
[TD]8%
[/TD]
[TD]42%
[/TD]
[TD]75%
[/TD]
[TD]25%
[/TD]
[TD]42%
[/TD]
[TD]75%
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

K
  • Question Question
conditional formatting question
Replies
2
Views
603
kerm007
K
P
  • Question Question
BAR COLOR
Replies
2
Views
203
Pietro Di Micio
P
S
  • Question Question
Sorting: Prioritizing Group retention over Order
Replies
0
Views
75
stretch2011
S
R
  • Solved
Remove unwanted #N/A errors in this LAMBDA/REDUCE formula
2
Replies
11
Views
674
Rnkhch
R
M
  • Question Question
Conditional formatting specifying 5 colours
Replies
7
Views
2K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top