Can index+match be used to add values together?
I'm in the process of creating a series of sheets to track weights submitted by a variety of people. The people are going to be categorized by 'team', and I already have an index+match formula that takes their submissions and automatically organizes them to like a main row per person, each value going into the column for the week it was submitted.
In the same row, they're going to have that identifying value for their 'team', and I'd like to create a formula that will add the submitted weights by people on that team together for whatever week, so I can then track the total percentage difference from submission to submission.
I know I can probably do it differently, by doing a simple addition formula and selecting those cells, but I'd like to be able to assign a team to a person and the formula automatically knows to add that person's submission to the total for that team.
Another question is should I do this in a separate sheet?
Best Answer

Basically SUMIFS will look through the entire range but only pull rows to add that have the matching criteria.
So if I wanted to add up all entries in {Column1} where {Column2} is "John Smith" but exclude entries that are not "John Smith", that's exactly what SUMIFS is designed for. I would reference all of {Column1}, but the built in range/criteria sets would only add up the numbers where {Column2} is "John Smith".
It looks to me like you are trying to sum the numbers in {Wk1} but only for rows where {TN} is equal to TeamName@row. In that case, you would use a SUMIFS like so:
=SUMIFS({Wk1}, {TN}, @cell = TeamName@row)
This will only grab the numbers from the {Wk1} range where {TN} is matching your criteria.
Answers

The index+match that I've been using basically works as such(logically, but not practically apparently);
Index({SubmissionFromWeek1},Match(TeamName@row, {TeamName},0)). Currently, it brings up a blank cell, and when there is no matching team name for it to look at it displays #NO MATCH.

It will display a value if there is only one instance of a team, but if there's multiple it doesn't give me anything. Guessing that Index+match isn't going to do it.

I also tried =(INDEX({Wk1}, MATCH(TeamName@row, {TN}, 1))) + (INDEX({Wk1}, MATCH(TeamName@row, {TN}, 1))), hoping that having the different search types would tell it to look from top to bottom and then from bottom to top to pull two separate values, but that just comes back with #NO MATCH

So I did mess around with SUMIFS, but my understanding is that it will only add the entire range of those cells if the requirements are met, but does not let you pick and choose which cells in that range get added?

Isn't that what you are trying to do? Sum certain cells based on a set of criteria?
Are you able to provide some screenshots for reference?

I am, but I'm trying to pull two numbers out of a column of 10+, and the range has to be the entire column, so I don't know how to make it only add those two numbers based on the criteria, rather than the whole range based on the criteria. And I think that's where SUMIFS may fall short? Unless I'm understanding it incorrectly. I just want something to pull two numbers out of a column and add them.
I did actually think of a solution last night. Since each 'team' will have 2 people, I'll just change the identifiers for the teams to Team1.1 & 1.2, Team 2.1 & 2.2, and so on.

(Thank's for the recommendations though)

Basically SUMIFS will look through the entire range but only pull rows to add that have the matching criteria.
So if I wanted to add up all entries in {Column1} where {Column2} is "John Smith" but exclude entries that are not "John Smith", that's exactly what SUMIFS is designed for. I would reference all of {Column1}, but the built in range/criteria sets would only add up the numbers where {Column2} is "John Smith".
It looks to me like you are trying to sum the numbers in {Wk1} but only for rows where {TN} is equal to TeamName@row. In that case, you would use a SUMIFS like so:
=SUMIFS({Wk1}, {TN}, @cell = TeamName@row)
This will only grab the numbers from the {Wk1} range where {TN} is matching your criteria.

OOH, I'll give that a shot!

So I must have just been formatting it incorrectly when I was messing with it yesterday! Thanks so much, I think that solved it.
Help Article Resources
Categories
Check out the Formula Handbook template!