SUMIF and Dynamic Reference

Question

I have a range of data named data_6120 and the column to sum is named datasum_6120 where 6120 is an account number.

As I have several account numbers, I would like to have the account number in B2.  And write the formula in b4 as   sumif("data_"&b2,a4,"datasum_"&b2).  If a simple ="data_"&b2 brings back data_6120, how do I make the sumif dynamic?  The criteria in cell A4 will always be static and a range name exists for each account number.

Answer 1

I have a range of data named data_6120 and the column to sum is named datasum_6120 where 6120 is an account number.

As I have several account numbers, I would like to have the account number in B2.  And write the formula in b4 as   sumif("data_"&b2,a4,"datasum_"&b2).  If a simple ="data_"&b2 brings back data_6120, how do I make the sumif dynamic?  The criteria in cell A4 will always be static and a range name exists for each account number.

Try this formula:

=SUMIF(INDIRECT("data_"&B2),A4,INDIRECT("datasum_"&B2))

Hope this helps / Lars-Åke

Answer 2

Are the ranges data_6120 etc. defined as a fixed range, for example

=Sheet3!$B$1:$B$50

or as a dynamic formula, e.g.

=OFFSET(Sheet3!$B$2, 0, 0, COUNTA(Sheet3!$B$2:$B$1000), 1)

If the former, you can use the INDIRECT function. That won't work with dynamically defined names though.