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
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