GreenTree
10-28-2008, 12:26 PM
Not sure if I should be using an array function, or a CountIf, or something else here. Any help will be much appreciated!
Here's the task: I have a grid which is populated by 2-letter ID's. Above the grid, I will have a line showing desired ID's. I'd like to compute a score for each row of the table showing how many entries match the desired ID's, in the same column. Simple example
Desired: AA DE BC
. Line 1 AB XA BC Score = 1 (BC)
. Line 2 AC BC DE Score = 0 (nothing matches the Desired ID in the same column)
. Line 3 AA DE JJ Score = 2 (AA and DE match)
The actual table is about 35 columns by 500 rows, and the Desired entires will be updated by the users. I could use another worksheet to create a duplicate grid populated by a statement like
=if ( {grid entry!R,C} = {Line 1 entry!R1,C}, 1, 0 )
and sum across the row to get the score, but I'd prefer to avoid the extra worksheet if it can be done. I also need to keep the workbook compatible with Open Office, which rules out using a VBA solution, so I need to do this all with formulas.
As best I can tell, CountIf looks at static comparisons, with no ability to refer to the cell at the top of the respective column. I'm not smart enough on array functions to figure out if some form of SumIf will work for this or not.
Anyone?
Many thanks,
G.T.
Here's the task: I have a grid which is populated by 2-letter ID's. Above the grid, I will have a line showing desired ID's. I'd like to compute a score for each row of the table showing how many entries match the desired ID's, in the same column. Simple example
Desired: AA DE BC
. Line 1 AB XA BC Score = 1 (BC)
. Line 2 AC BC DE Score = 0 (nothing matches the Desired ID in the same column)
. Line 3 AA DE JJ Score = 2 (AA and DE match)
The actual table is about 35 columns by 500 rows, and the Desired entires will be updated by the users. I could use another worksheet to create a duplicate grid populated by a statement like
=if ( {grid entry!R,C} = {Line 1 entry!R1,C}, 1, 0 )
and sum across the row to get the score, but I'd prefer to avoid the extra worksheet if it can be done. I also need to keep the workbook compatible with Open Office, which rules out using a VBA solution, so I need to do this all with formulas.
As best I can tell, CountIf looks at static comparisons, with no ability to refer to the cell at the top of the respective column. I'm not smart enough on array functions to figure out if some form of SumIf will work for this or not.
Anyone?
Many thanks,
G.T.