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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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
585
kerm007
K
P
  • Question Question
BAR COLOR
Replies
2
Views
201
Pietro Di Micio
P
S
  • Question Question
Sorting: Prioritizing Group retention over Order
Replies
0
Views
71
stretch2011
S
R
  • Solved
Remove unwanted #N/A errors in this LAMBDA/REDUCE formula
2
Replies
11
Views
562
Rnkhch
R
M
  • Question Question
Conditional formatting specifying 5 colours
Replies
7
Views
2K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

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