Referring to named ranges in SUMIF (potentially address function required)

[foshizzle]

Hi all,

Would greatly appreciate some help in referring to named ranges . Searched around for a long time to no avail. Please see excel file as reference.

I have created a named range called 'animals' (B3:B6) and I would like to create a sumif formula by referring to a cell which then refers to that named range.

e.g. =sumif(animals,"dog",C3:C6) gives me 1. This is great, however I am trying to replace animals here with a cell (I am doing this because in relaity i have more than 1 named range and would like to drag down instead of manually writing the named range).

I have tried to use address,row,rows,column,columns function to give me the range for my sumif but does not work as seen in cell E6 (address function in E2)

When I enter in E6 =sumif(E2,"dog"C3:C6) i get 0 instead of 1

Would appreciate any help with this.

Thanks

[Olly]

Assuming you want to put the named range name in a cell, then reference that, then you can use the INDIRECT function.

For example: put "animals" in cell E1, then use:

Please Login or Register  to view this content.