1

I have a spreadsheet that will have the text "Number Returned" multiple times in a header row (running from A1-Z1, but will expand and search area will be larger). From A2:A55 I have names, I need a way to match the name to each instance of "Number Returned" and get a sum. I see I can use =Index(Match(Match)) to find one instance of "Number Returned" but could that formula be altered to add to a SUM() formula?

Here are a few snippets to show what I want (the total would need to be on a secondary worksheet not the same one if that matters)

enter image description here

enter image description here

Improve this question

1 Answer 1

Reset to default
2

You can simply use this SUMIF formula against your data:

=SUMIF($A$1:$M$1,"Number Returned",A2:M2)

by placing it in N2 and copying down. (you can adjust the end range and formula placement)

To have the formula on a separate sheet, the formula provided by @ScottHoltzman in the comments may work better.

=SUMIF(Sheet2!$A$1:$M$1,"Number Returned",OFFSET(Sheet2!$A$1:$M$1,MATCH(A2,Sheet2!$A$1:$A$7,0)-1,0))

The range qualifications in the formula are based on your example data with the following notes:

  • Sheet2 refers to where the actual data is
  • assumes the reference list is columns A:B of with Header (Total Return) being row 1

As it was @ScottHoltzman who came up with a better answer, this has been made a community wiki.

If avoiding volatile formulas is a concern, the following replaces the OFFSET function's role with the non-volatile INDEX function.jpd

=SUMIF(Sheet2!$A$1:$M$1, "Number Returned", INDEX(Sheet2!$A:$M, MATCH(A2, Sheet2!$A:$A, 0), 0))
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Does this follow the same order of the names listed in A2:Z2?
since user wants formula on secondary sheet, this could be more dynamic by finding the exact row of the string and using offset to set the row to sum ... =SUMIF(Sheet2!$A$1:$Z$1,"Number Returned",OFFSET(Sheet2!$A$1:$Z$1,MATCH(A2,Sheet2!$A$1:$A$7,0)-1,0)) .. assumes data is on Sheet2 and letter list starts in A2
@ScottHoltzman and FartStopper I failed to read to the end of the post. Scott's answer would be the best. Scott throw yours into a new answer.
@ScottCraner - that's okay. You have a great answer. I just added the comment as a way to take it a step further. Your answer works as well, if you qualify the sheets and ensure the string are in the exact same order, which is shown in the examples :)
@ScottHoltzman, I added yours and made it a community wiki. Feel free to edit. I may not have explained it thorough enough.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.