Virtual Counting Of Conditional Formatting

nonwally

New Member
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.

TIA

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.

Hey that works !!!

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

Many thanks.

Replies
3
Views
488
Replies
3
Views
237
Replies
1
Views
619
Replies
8
Views
411
Replies
1
Views
260

1,217,757
Messages
6,138,429
Members
450,137
Latest member
HANHAN

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.

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

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