SUMIF With RangeNames (XP; SR3)

Topic

New Reply

I would like to utilize range names in a SUMIF formula. The range name would refer to the column letter the range names was entered in.

As an example:
RangeName entered in Cell K1 = MyRangeName

Without using range names the formula would be: =SUMIF(A:A,B5,K:K)

I was thinking of something like: =SUMIF(A:A,B5,LEFT(ADDRESS(1,COLUMN(MyRangeName),4),LEN(ADDRESS(1,COLUMN(MyRangeName),4))-1)&”:”&LEFT(ADDRESS(1,COLUMN(MyRangeName),4),LEN(ADDRESS(1,COLUMN(MyRangeName),4))-1))

The reason I’m going with the range name approach is that the formula will eventually be on a different sheet and the range will move as new columns are inserted on the other sheet.

Any thoughts would be appreciated,
John

Reply | Quote

Replies

I am not at all sure what you are trying to accomplish, but if you name the range K:K MyRangeName, then you should be able to do this:

=SUMIF(A:A,B5,MyRangeName)

Reply | Quote

Legare,

I never thought to name the entire column as the range name. It worked.

Thanks for the suggestion,
John

Reply | Quote

Since you are on Excel 2003, you could turn the data area into a list by running Data|List|Create List. The formulas that refer the ranges from a such list will automatically update whenever you add new records to the data area. The setup makes named ranges unnecessary.

Reply | Quote