Indirect sum formula

[tasander]

Hi

I want to have a formula which can look up the row of a certain item and then do a sum on that entire row.

So far i can do a match formula to pull back the row number that i need but i am unable to find a way to map this into another formula. If i was to write the formula out (without calculating the row) it would be

=SUM('[Country file.xls]Austria'!$35:$35)

My formual for working out the row is

=MATCH(B4& " " &D3,'[Country File.xls]Austria'!$A:$A,0)

Can i put the second formula into the first formula (red text) somehow so that if the row changes then the formula will still work.

Many Thanks

 

[gsistek]

Try:
=SUM(INDIRECT("'[Country file.xls]Austria'!"&MATCH(B4& " " &D3,'[Country File.xls]Austria'!$A:$A,0)&":"&MATCH(B4& " " &D3,'[Country File.xls]Austria'!$A:$A,0))

 

[Jonmo1]

I would recommend INDEX instead of INDIRECT

=SUM(INDEX('[Country file.xls]Austria'!$A:$IV,Match(....),0))

 

[T. Valko]

Try:
=SUM(INDIRECT("'[Country file.xls]Austria'!"&MATCH(B4& " " &D3,'[Country File.xls]Austria'!$A:$A,0)&":"&MATCH(B4& " " &D3,'[Country File.xls]Austria'!$A:$A,0))

Note that if using INDIRECT the source file must be open for the formula to work correctly.

 

[Jonmo1]

Note that if using INDIRECT the source file must be open for the formula to work correctly.

1 good reason to use Index instead..
Plus performance should be better with Index.

 

[tasander]

That's perfect

Thanks