Afternoon All,
I am attempting to combine the INDIRECT and ROW/S functions to return an array.
<tbody>
</tbody>
In column A I have the text reference to the address i need to work with
(O,U,AA etc)
What I need to do is combine this text with the ROW/S function so that when all is calculated it returns the array in the following format.
In essence for the formula above I am adding the total time if it is greater than 0 giving Total number of times visited
Than once i copy down the rows the ROW/S function will increment
So the formula above would become
So if I input the formula into D9 then
$A$2 & ROWS() evaluates to "O9"
$A$3 & ROWS() evaluates to "U9"
$A$4 & ROWS() evaluates to "AA9"
But in D10...
$A$2 & ROWS() evaluates to "O10"
$A$3 & ROWS() evaluates to "U10"
$A$4 & ROWS() evaluates to "AA10"
I have attempted to remove quotes, use concatenate
Any help would be much appreciated.
Thank you in advance
Ross
I am attempting to combine the INDIRECT and ROW/S functions to return an array.
A | B | C | D | E |
O | LARGE | |||
U | DATASET | |||
AA | TO THE | |||
AG | RIGHT |
<tbody>
</tbody>
In column A I have the text reference to the address i need to work with
(O,U,AA etc)
What I need to do is combine this text with the ROW/S function so that when all is calculated it returns the array in the following format.
Code:
=SUM(COUNTIF(INDIRECT({"O9","U9","AA9","AG9","AM9","AS9","AY9","BE9","BK9","BQ9","BW9","CC9","CI9","CO9","CU9","DA9","DG9","DM9","DS9","DS9","EE9","EK9","EQ9","EW9","FC9","FI9","FO9","FU9","GA9","GG9","GM9","GS9","GY9","HE9","HK9","HQ9","HW9","IC9","II9","IO9","IU9","JA9","JG9","JM9","JS9","JY9","KE9","KK9","KQ9","KW9","LC9","LI9","LO9"}),">0"))
In essence for the formula above I am adding the total time if it is greater than 0 giving Total number of times visited
Than once i copy down the rows the ROW/S function will increment
So the formula above would become
Code:
=SUM(COUNTIF(INDIRECT({"$A$2 & ROWS()","$A$3 & ROWS()","$A$4 & ROWS()","$A$5 & ROWS()...}),">0"))
So if I input the formula into D9 then
$A$2 & ROWS() evaluates to "O9"
$A$3 & ROWS() evaluates to "U9"
$A$4 & ROWS() evaluates to "AA9"
But in D10...
$A$2 & ROWS() evaluates to "O10"
$A$3 & ROWS() evaluates to "U10"
$A$4 & ROWS() evaluates to "AA10"
I have attempted to remove quotes, use concatenate
Any help would be much appreciated.
Thank you in advance
Ross