Use of Indirect function within SUM with multiple criteria

[crackod93]

Hi all,

Wondering if anyone has any ideas on the best way to use this INDIRECT function within this scenario...

Example Array Formula:

{=SUM((Data!$C$8INDIRECT("Data!$C"&maxrow))="Active")*(Data!$S$8INDIRECT("Data!$S"&maxrow))="Y")*(Data!$AS$8INDIRECT("Data!$AS"&maxrow))="G"))}

'maxrow' is a reference which counts the number of rows in use on the sheet (ie non-blank) = {=MAX(ROW(8:2999)*(A8:A2999<>""))}

Now the question that i have is that if a user inserts any columns into the 'Data' sheet, then only part of the formula is automatically updated with the new column reference, whereas the pieces which are within the INDIRECT brackets do not change, example:

{=SUM((Data!$D$8INDIRECT("Data!$C"&maxrow))="Active").....

The column letter in the INDIRECT part still shows as 'C', but the value should have updated to D (yes im aware that im fixing this position as the indirect part has the column letter in quotes)

So what im wondering is, how can i move this part out of the INDIRECT function whilst leaving the 'maxrow' part in - something like this:

{=SUM((Data!$C$8:$C(INDIRECT(""&maxrow))="Active")......

Ideas anyone???

thanks

 

[RoryA]

You could use INDEX:

=SUM((Data!$C$8:INDEX(Data!$C:$C,maxrow)="Active")*(Data!$S$8:INDEX(Data!$S:$S,maxrow)="Y")*(Data!$AS$8:INDEX(Data!$AS:$AS,maxrow)="G"))

 

[crackod93]

hi, thank you for the very quick reply, just checked this quickly and it works a treat, hadnt even thought about using INDEX!!

thanks very much!!

 

[RoryA]

Also has the advantage of only being semi-volatile, unlike INDIRECT.