Products Search… 1 1
Home
Selecting a Specific Column of a Named Range for the SUMIF Function Ask Question
PUBLIC Asked 4 years, 4 months ago Active 1 year, 2 months ago Viewed 31k times
Questions
I am trying to create a SUMIF function that dynamically adds up values in a specific column of a
Tags The Overflow Blog
named range in my Excel sheet.
Users 16 How often do people actually copy and
It is very easy to do this when there is no named range : paste from Stack Overflow? Now we
FIND A JOB
know.
Jobs
Podcast 331: One in four visitors to Stack
Companies 3 Overflow copies code
TEAMS Featured on Meta
Stack Overflow for Stack Overflow for Teams is now free for
Teams – Collaborate up to 50 users, forever
and share knowledge The formula picks out all the cells that contain "London" in their name and sums up the expenses
with a private group. Outdated Answers: results from use-case
related to London. survey
What I am trying to do is to use a named range called TripsData (A2:B5) and tell the SUMIF Downvotes Survey results
function to sum the entries in the column 2 of this range that meet the criterion of having London in
Hot Meta Posts
their name.
How can I make this work without needing to create a second named range for column 2 and 25 A ton of pending flags, should I continue
flagging?
Create a free Team simply by telling Excel to look within the specified column of this named range? Index/Match only
What is Teams? return one value so that doesn't work when there are several cells with London in their name. 14 Obvious copied answer not being deleted
Thanks for your help!
Linked
excel excel-formula offset sumifs 3 SumIF Using Table/Named Range Instead
of Single Cell Criteria
Share Edit Follow edited Feb 2 '20 at 22:28 asked Nov 24 '16 at 21:20 Related
chris neilsen Cla Rosie
48.2k 10 77 112 223 1 2 8 2 In Excel 2007, how can I SUMIFS indices
of multiple columns from a named range?
Add a comment 2 Excel SUMIF variable date range
2 Sumifs formula where sum range changes
3 Answers Active Oldest Votes
depending on Cell Value
5 SUMIFS with date and name criteria…
Use INDEX to refer to a specific column in the named range (it can refer to a whole column), like month and year only
this 0 dynamically change column in excel sumifs
19
=SUMIF(TripsData,"*London*",INDEX(TripsData,,2)) 1 Sumifs Dynamic Range
3 SumIF Using Table/Named Range Instead
of Single Cell Criteria
Share Edit Follow answered Nov 24 '16 at 21:28
chris neilsen
Hot Network Questions
48.2k 10 77 112
What is a common idiom meaning, 'that a situation
1 Great tip! I never figured out that I can leave the second argument of INDEX method empty. – Mi-La Jan 9 is likely to change all of a sudden without notice or
'18 at 17:04 reason'?
What counts as "Java software experience" after
2 Just remember, as with VLOOKUP, that if you add/remove columns to the named range before the index
all?
column, your formula will not return the same column as it did. – Applez00800 Jun 4 '18 at 23:46
Is an integer variable constant or logarithmic
Add a comment space?
Why are "Attefallshus" (Attefall houses) in Sweden
not allowed to be on a land without a big house
You can do that without any named ranges at all, if you turn your data into an Excel Table object. already on it?
Select any cell in the range or the whole range and click Insert > Table or hit Ctrl - T . Why was the northern boundary of the Mongol
6 empire set where it was?
There will be a dialog that asks if your table has headers. Yours does. Now you can reference the Can you lower your AC on purpose?
table and its columns by their inherent names and build your formula like this:
Why is Starliink polluting the night sky a big
concern if we have space telescopes?
=SUMIF(Table1[Expense],"*London*",Table1[Cost]) Why do some electromagnetic waves continue
travelling while others disappear?
How does Ingenuity measure its altitude when
flying?
Drawing a person in latex
My DIY RGB LED panel powered by 9V battery
works but certain colors like blue, white, and
purple don't work
Reversencode the given string
Offering students a job
Does the Zealot Barbarians Warrior of the Gods
You can rename the table, of course, even after the formula is in place. When you click a cell in the feature work with Divine Soul Twinned Spell?
table, there will be a new ribbon for commands that relate to tables only. It's a very powerful tool. Virtualbox VM lags after increasing #CPU
Why didn't Hermione try to get Slughorn's
Any formulas, formatting etc. that apply to a whole table column will automatically carry over into memory?
new table rows. The table column reference will adjust automatically, too, of course, so you don't Is there a directed angle symbol?
have to mess with dynamic range names or re-define what a named range applies to.
How many bystanders will help?
Note: the formula uses structured referencing instead of cell addresses. This option can be turned Is it ok to ask for documentations on the software
and code that I will be working on even though I
off by clicking File > Options > Formulas > tick or untick "Use table names in formulas"
didn't start the work yet
Does every countable set of Turing degrees have
Share Edit Follow answered Nov 24 '16 at 22:17 an upper bound, without AC?
teylyn Is it better to walk or run in the rain?
30.7k 3 48 62
Voting system with two votes as a defense against
fear of voting for a loser - is it anywhere in the
I've started using Tables for my main data tables for this very reason - how will using several small tables, world?
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! – Sean Feb 5 '20 at What is the consequence of using lag bolts without
19:46 pilot holes?
WW2-era bureaucrats escaping nuclear war into
@Sean Pivot tables and Excel tables are not the same thing. Excel tables won't impact on performance. –
Lovecraftian dimension
teylyn Feb 6 '20 at 6:22
Thank you! I've been incorporating Tables a lot more into my workbooks and they are working out very Question feed
well. – Sean Feb 13 '20 at 22:55
Add a comment
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
2 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.
Share Edit Follow edited Aug 15 '19 at 2:32 answered Aug 14 '19 at 23:25
Snel23 user11929574
1,360 1 9 19 21 1
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 –
chris neilsen Oct 20 '19 at 9:58
could use match to determine the column number for the index. – eli-k Dec 13 '20 at 12:44
Add a comment
Your Answer
Links Images Styling/Headers Lists Blockquotes Code HTML Tables Advanced help
Post Your Answer
Not the answer you're looking for? Browse other questions tagged excel excel-formula offset sumifs
or ask your own question.
STACK OVERFLOW PRODUCTS COMPANY STACK EXCHANGE Blog Facebook Twitter LinkedIn Instagram
NETWORK
Questions Teams About
Technology
Jobs Talent Press
Life / Arts
Developer Jobs Directory Advertising Work Here
Culture / Recreation
Salary Calculator Enterprise Legal
Science
Help Privacy Policy
Other
Mobile Terms of Service
Disable Responsiveness Contact Us
Cookie Settings
site design / logo © 2021 Stack Exchange Inc; user contributions
Cookie Policy licensed under cc by-sa. rev 2021.4.19.39103
