Can index+match be used to add values together?

Jessie Davis KRNW

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?

Paul Newcome

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.

Jessie Davis KRNW

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.

Jessie Davis KRNW

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.

Jessie Davis KRNW

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

Paul Newcome

Try a SUMIFS instead.

Jessie Davis KRNW

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?

Paul Newcome

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?

Jessie Davis KRNW

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.

Jessie Davis KRNW

(Thank's for the recommendations though)

Paul Newcome

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.

Jessie Davis KRNW

OOH, I'll give that a shot!

Jessie Davis KRNW

So I must have just been formatting it incorrectly when I was messing with it yesterday! Thanks so much, I think that solved it.

Paul Newcome

Happy to help. 👍️