Virtual Counting Of Conditional Formatting


New Member
Jul 29, 2010
In all the samples of arrays and sumproducts I've seen, most seem to use fixed criteria which doesn't help me.

Here is a brief sample of the problem I am faced with

Row 1 is a count / results row. No data here (yet). This is where the a successful formula will go.

Row 2 is for headings (A to E): Number, Select 1, Select 2, Select 3, Select 4

Rows 3 to 36, column A: A number, decimal, unsorted, non-unique (duplicates but unique numbers can also be found).

In the other columns, the user selects 0 or 1 (unselected or selected). Each of these columns works independantly of the other select columns.

I have a working conditional formula that detects if a duplicate number is selected (1) more than once in any given select column (B to E).
The working conditional formula is:

It works beautifully, turning cells red when duplicate numbers are selected.

Now the problem ...

Up in row 1, I, effectively & virtually, want to count the number of times the conditional formula is triggered.

I know that accessing conditional formulas is a bit on the difficult side, so instead I am trying to replicate the conditional formula so it produces a count of simulated conditional triggers for each column B to E.

So if 2 or more rows have a number of 123.45 in column A, and all cells in column B are 0 ...

When the user enters a 1 alongside a 123.45, 0 is still shown by the required formula ...

However when a 1 is entered alongside another 123.45, a 1 is shown by the required formula.

Please note I cannot test for 123.45 in the application, because that will not produce the dynamic result I require. I am trying to prevent duplicate numbers being selected more than once.

Please help :)

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.


MrExcel MVP
Apr 25, 2006
Hi nonwally
Welcome to the board

Try for column B:


This in an array formula and so MUST be confirmed with CTRL+SHIFT+ENTER and not just ENTER.


New Member
Jul 29, 2010
Hey that works !!!

I thought it might be an array solution, tried a few different ways, but never tried the frequency formula.

Many thanks. :)

Watch MrExcel Video

Forum statistics

Latest member
Toby Corner

This Week's Hot Topics