Lookup to subtract and show difference - SUMIFS? SUMPRODUCT?

[Churchy LaFemme]

I want to show the change between two values over time on a summary tab. My data set has the first value and the second (typically larger) value. The data set will change so I need the summary tab to update.

The table below shows a sample of data, and then the column Season 2 Increase shows the expected result.

	Season 1 Cheeseburgers</SPAN>	Season 2 Cheeseburgers</SPAN>		Season 2 Increase</SPAN>
Teen Wolf</SPAN>	57</SPAN>	62</SPAN>		5</SPAN>
Derek</SPAN>	22</SPAN>	56</SPAN>		34</SPAN>
Stiles</SPAN>	38</SPAN>	102</SPAN>		64</SPAN>
Jackson</SPAN>	44</SPAN>	9</SPAN>		-35</SPAN>

<TBODY>
</TBODY>

Maybe I'm having a Friday afternoon problem, but I just can't make this work. My data is in a VERY LARGE table and it would not be useful to have the summary results on that tab. Additionally, there are other formulas in the summary that make using a pivot out of the question.

What I want to do is refer to my data set, and where the name is "Stiles," subtract season 1 from season 2.

If I can't make this work with a formula, I can put the calc in the data set, and then use another lookup to reference the cell in that column that matches the name. But I'd rather not add yet another column to the data set if I can help it!

Thank you!

 

[iyyi]

What I want to do is refer to my data set, and where the name is "Stiles," subtract season 1 from season 2.

If I can't make this work with a formula, I can put the calc in the data set, and then use another lookup to reference the cell in that column that matches the name. But I'd rather not add yet another column to the data set if I can help it!

Thank you!

If you have "Stiles" in A2 in the Summary Sheet then try this formula:
=SUMPRODUCT((Summary!A2=Data!A2:A1000)*(Data!C2:C1000-Data!B2:B1000))
Write your number of rows instead of the 1000.

 

[Churchy LaFemme]

Super! This worked like a charm. Not sure why I wasn't getting it when I fooled around with SUMPRODUCT
.

Thank you!