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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,791
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. :)
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,709
Messages
5,512,965
Members
408,928
Latest member
Black Vinyl

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top