Suming an area of data where you match two criterias

[gheyman]

I have peoples names down column A (Starting in A2) and Years across the row 1 (Starting in B1)

Then I have how much each persons Sales were for each year (This goes down to Row 564)

I need a formula where I can find how much Joe Smith (value down Column A) made in 2016.


On a separate sheet I two cells, one that has the list of names and the second with the list of years. The user selects a name and then the year. Based on those two criteria's I want to find the corresponding value. I need a formula that can find this value. (I will be able to alter what you send so its looking for the two aforementioned fields.)

Please, I don't want alternative ways to get to that value. I know about Filters, Pivot tables....

Thanks!

 

[Joe4]

I think you should be able to use SUMIFS to do this for you.
See: https://www.techonthenet.com/excel/formulas/sumifs.php

And there is no problem using Sheet references in the ranges of that formula to reference other sheets.

If that doesn't seem to work, please post a small example of your data (maybe I am not understanding your data structure from your description).

 

[Marcelo Branco]

See if this example helps

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Name​	2015​	2016​	2017​	2018​		Name​	Year​	Result​
2​	Anthony​	10​	11​	12​	9​		Joe Smith​	2016​	14​
3​	Joe Smith​	12​	14​	13​	11​		Mary​	2018​	13​
4​	Mary​	8​	15​	14​	13​

<tbody>
</tbody>

Criteria in columns G:H

Formula in I2 copied down
=INDEX($B$2:$E$4,MATCH(G2,$A$2:$A$4,0),MATCH(H2,$B$1:$E$1,0))

Adjust the ranges to suit.

M.

 

[Joe4]

See if this example helps

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Name	2015​	2016​	2017​	2018​		Name	Year	Result
2​	Anthony	10​	11​	12​	9​		Joe Smith	2016​	14​
3​	Joe Smith	12​	14​	13​	11​		Mary	2018​	13​
4​	Mary	8​	15​	14​	13​

<tbody>
</tbody>

Criteria in columns G:H

Formula in I2 copied down
=INDEX($B$2:$E$4,MATCH(G2,$A$2:$A$4,0),MATCH(H2,$B$1:$E$1,0))

Adjust the ranges to suit.

Yep, I am guessing that is probably correct and I misunderstood the data construct (things are so much clearer when you can see an example!).

 

[gheyman]

SUMIFS( sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ... criteria_range_n, criteria_n] )

How could a SumIfs work? I would have to define the "sum_Range" which would mean I could only find the value for one year.

2010 2011 2012 2013 2014
Joe Smith 505 124 178 604 254
Jill Brown 556 654 787 245 456


If I wanted to Find what Joe Smith sold in 2011 using a SumIfs I would have to define Column C as the Sum_Range in an SumIfs. That's great if all I cared about was year 2011 But I need the formula to be dynamic and so If I say Joe Smith 2011 it gives me 124. If I say Joe Smith 2013 it gives me 604. I cant go changing the Sum_Range in the SumIfs everytime I need to see a different year.

 

[Joe4]

See Marcelo's response. I think he understood your data structure better than I did.