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


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Name​

[/TD]
[TD]

2015​

[/TD]
[TD]

2016​

[/TD]
[TD]

2017​

[/TD]
[TD]

2018​

[/TD]
[TD][/TD]
[TD]

Name​

[/TD]
[TD]

Year​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Anthony​

[/TD]
[TD]

10​

[/TD]
[TD]

11​

[/TD]
[TD]

12​

[/TD]
[TD]

9​

[/TD]
[TD][/TD]
[TD]

Joe Smith​

[/TD]
[TD]

2016​

[/TD]
[TD]

14​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Joe Smith​

[/TD]
[TD]

12​

[/TD]
[TD]

14​

[/TD]
[TD]

13​

[/TD]
[TD]

11​

[/TD]
[TD][/TD]
[TD]

Mary​

[/TD]
[TD]

2018​

[/TD]
[TD]

13​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Mary​

[/TD]
[TD]

8​

[/TD]
[TD]

15​

[/TD]
[TD]

14​

[/TD]
[TD]

13​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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


[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]Name[/TD]
[TD]

2015​

[/TD]
[TD]

2016​

[/TD]
[TD]

2017​

[/TD]
[TD]

2018​

[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]Year[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]Anthony[/TD]
[TD]

10​

[/TD]
[TD]

11​

[/TD]
[TD]

12​

[/TD]
[TD]

9​

[/TD]
[TD][/TD]
[TD]Joe Smith[/TD]
[TD]

2016​

[/TD]
[TD]

14​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]Joe Smith[/TD]
[TD]

12​

[/TD]
[TD]

14​

[/TD]
[TD]

13​

[/TD]
[TD]

11​

[/TD]
[TD][/TD]
[TD]Mary[/TD]
[TD]

2018​

[/TD]
[TD]

13​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]Mary[/TD]
[TD]

8​

[/TD]
[TD]

15​

[/TD]
[TD]

14​

[/TD]
[TD]

13​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



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.