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.