Selecting a Specific Column of a Named Range for the SUMIF Function

Question

I am trying to create a SUMIF function that dynamically adds up values in a specific column of a named range in my Excel sheet.

It is very easy to do this when there is no named range :

The formula picks out all the cells that contain "London" in their name and sums up the expenses related to London.

What I am trying to do is to use a named range called TripsData (A2:B5) and tell the SUMIF function to sum the entries in the column 2 of this range that meet the criterion of having London in their name.

How can I make this work without needing to create a second named range for column 2 and simply by telling Excel to look within the specified column of this named range? Index/Match only return one value so that doesn't work when there are several cells with London in their name.

Thanks for your help!

Beisel

Answer 1

Use INDEX to refer to a specific column in the named range (it can refer to a whole column), like this

=SUMIF(TripsData,"*London*",INDEX(TripsData,,2))

Amnion

Great tip! I never figured out that I can leave the second argument of INDEX method empty. – Pendergrass

Just remember, as with VLOOKUP, that if you add/remove columns to the named range before the index column, your formula will not return the same column as it did. – Incentive

Answer 2

You can do that without any named ranges at all, if you turn your data into an Excel Table object. Select any cell in the range or the whole range and click Insert > Table or hit Ctrl-T.

There will be a dialog that asks if your table has headers. Yours does. Now you can reference the table and its columns by their inherent names and build your formula like this:

=SUMIF(Table1[Expense],"*London*",Table1[Cost])

You can rename the table, of course, even after the formula is in place. When you click a cell in the table, there will be a new ribbon for commands that relate to tables only. It's a very powerful tool.

Any formulas, formatting etc. that apply to a whole table column will automatically carry over into new table rows. The table column reference will adjust automatically, too, of course, so you don't have to mess with dynamic range names or re-define what a named range applies to.

Note: the formula uses structured referencing instead of cell addresses. This option can be turned off by clicking File > Options > Formulas > tick or untick "Use table names in formulas"

Tightrope

I've started using Tables for my main data tables for this very reason - how will using several small tables, to replace several named ranges, impact the size and performance of my workbook? I have run into issue with too many pivot tables really bloating and bogging down my workbooks. Thanks! – Abjuration

@Abjuration Pivot tables and Excel tables are not the same thing. Excel tables won't impact on performance. – Tightrope

Thank you! I've been incorporating Tables a lot more into my workbooks and they are working out very well. – Abjuration

Answer 3

You can use Chris' idea of Index(Table1,,Col#) with the named range "Table1" (without creating an Excel table Object if you don't want to for some reason) and STILL avoid the problem Applez mentions in the comment below Chris' idea. Applez warns that using a constant for a column number reference is dangerous if you later insert another column before that column in the named range. You will find that Excel does NOT auto increment the constant, so your formula breaks.

Applez is right..... so DON'T use a constant, use a column number "reference" instead of a constant. For example....

=SUMIF(TripsData,"*London*",INDEX(TripsData,,Column(B1)))

If you later insert a column between A and B, Excel WILL auto increment the reference Column(B1) to Column(C1). Just don't delete B1 or Row 1 or you will get a REF error. I usually use the the header/tile "cell" (in whatever row that is in) for that table column within the Column reference (as it is highly unlikely I will ever delete the header/title cell of column of a table unless I delete the entire column). In this particular example as it turn out, B1 "IS" the the title/header cell for that column in the data table. So that is what I used for the example.

Ramires

Generally good advice, but assumes Table1 starts in column A. While the OPs version does, that may not always be the case. A more robust version would be Column(B1) - Column(Table1) +1 – Amnion

could use match to determine the column number for the index. – Ontiveros

Answer 4

Awesome formula, just in case anyone needs to use a similar approach to FILTER a range. I used this approach

pmGendHC is the range I wanted to filter (I expect a spilled range with my data) I needed a colum (column number 13) to be different than 0

=FILTER(pmGendHC,INDEX(pmGendHC,,13)<>0) 

Agustin