Virtual Counting Of Conditional Formatting

nonwally

New Member
Joined
Jul 29, 2010
Messages
4
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:
=SUMPRODUCT(($A$3:$A$36=$A3)*(B$3:B$36<>0))>1

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 :)
TIA
 

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.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,827
Hi nonwally
Welcome to the board

Try for column B:

=SUM(--(FREQUENCY(IF(B$3:B$36>0,$A$3:$A$36),IF(B$3:B$36>0,$A$3:$A$36))>1))

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

nonwally

New Member
Joined
Jul 29, 2010
Messages
4
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

Threads
1,108,844
Messages
5,525,186
Members
409,630
Latest member
Toby Corner

This Week's Hot Topics

Top