Excel Formula: Match 2 Criteria in Column & Sum up all Rows

[Apple1]

Hi

I want an Excel formula that will allow
1) match the Year in Row1, and match the Sales or Profit in Row 2
2) sum all the rows in the matched column

EG, I want Year=2001 & Sales, so the formula will sum = 8559 + 4560 +1520 +1563

Thank you!


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]2001
[/TD]
[TD]2001
[/TD]
[TD]2002
[/TD]
[TD]2002
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sales
[/TD]
[TD]Profit
[/TD]
[TD]Sales
[/TD]
[TD]Profit
[/TD]
[/TR]
[TR]
[TD]James
[/TD]
[TD]1000
[/TD]
[TD]690
[/TD]
[TD]8559
[/TD]
[TD]1895
[/TD]
[/TR]
[TR]
[TD]Peter
[/TD]
[TD]2000
[/TD]
[TD]756
[/TD]
[TD]4560
[/TD]
[TD]1596
[/TD]
[/TR]
[TR]
[TD]Tom
[/TD]
[TD]3000
[/TD]
[TD]56
[/TD]
[TD]1520
[/TD]
[TD]3698
[/TD]
[/TR]
[TR]
[TD]Edwin
[/TD]
[TD]4000
[/TD]
[TD]56
[/TD]
[TD]1563
[/TD]
[TD]4563
[/TD]
[/TR]
</tbody>[/TABLE]

 

[admiral100]

Hi,

Try this : Ctrl+Shift+Enter NOT just Enter

=SUM(IF($B$2:$E$2="Sales",IF($B$1:$E$1=2002,$B$3:$E$6)))

 

[MARZIOTULLIO]

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

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

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

1​

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

2001​

[/TD]
[TD]

2001​

[/TD]
[TD]

2002​

[/TD]
[TD]

2002​

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

2002​

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

2​

[/TD]
[TD][/TD]
[TD]Sales[/TD]
[TD]Profit[/TD]
[TD]Sales[/TD]
[TD]Profit[/TD]
[TD][/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]

3​

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

1000​

[/TD]
[TD]

690​

[/TD]
[TD]

8559​

[/TD]
[TD]

1895​

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

16202​

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

4​

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

2000​

[/TD]
[TD]

756​

[/TD]
[TD]

4560​

[/TD]
[TD]

1596​

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

5​

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

3000​

[/TD]
[TD]

56​

[/TD]
[TD]

1520​

[/TD]
[TD]

3698​

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

6​

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

4000​

[/TD]
[TD]

56​

[/TD]
[TD]

1563​

[/TD]
[TD]

4563​

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

G2=SUMPRODUCT(($B$1:$E$1=G1)*($B$2:$E$2=G2)*($B$3:$E$6))

 

[Aladin Akyurek]

Let H1 houses 2001 and I1 Sales.

In G1 just enter:

=SUM(INDEX(B3:E6,0,MATCH(H1,INDEX(B1:E1,MATCH(I1,$B$2:$E$2,0),0),0)))

 

[MARZIOTULLIO]

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

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

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

1​

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

2001​

[/TD]
[TD]

2001​

[/TD]
[TD]

2002​

[/TD]
[TD]

2002​

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

2002​

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

2​

[/TD]
[TD][/TD]
[TD]Sales[/TD]
[TD]Profit[/TD]
[TD]Sales[/TD]
[TD]Profit[/TD]
[TD][/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]

3​

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

1000​

[/TD]
[TD]

690​

[/TD]
[TD]

8559​

[/TD]
[TD]

1895​

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

16202​

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

4​

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

2000​

[/TD]
[TD]

756​

[/TD]
[TD]

4560​

[/TD]
[TD]

1596​

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

5​

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

3000​

[/TD]
[TD]

56​

[/TD]
[TD]

1520​

[/TD]
[TD]

3698​

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

6​

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

4000​

[/TD]
[TD]

56​

[/TD]
[TD]

1563​

[/TD]
[TD]

4563​

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

G3=SUMPRODUCT(($B$1:$E$1=G1)*($B$2:$E$2=G2)*($B$3:$E$6))

forumula in g3

 

[Agahi90ir]

very very good
thanks

 

[Apple1]

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

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

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

1​

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

2001​

[/TD]
[TD]

2001​

[/TD]
[TD]

2002​

[/TD]
[TD]

2002​

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

2002​

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

2​

[/TD]
[TD][/TD]
[TD]Sales[/TD]
[TD]Profit[/TD]
[TD]Sales[/TD]
[TD]Profit[/TD]
[TD][/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]

3​

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

1000​

[/TD]
[TD]

690​

[/TD]
[TD]

8559​

[/TD]
[TD]

1895​

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

16202​

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

4​

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

2000​

[/TD]
[TD]

756​

[/TD]
[TD]

4560​

[/TD]
[TD]

1596​

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

5​

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

3000​

[/TD]
[TD]

56​

[/TD]
[TD]

1520​

[/TD]
[TD]

3698​

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

6​

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

4000​

[/TD]
[TD]

56​

[/TD]
[TD]

1563​

[/TD]
[TD]

4563​

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

G2=SUMPRODUCT(($B$1:$E$1=G1)*($B$2:$E$2=G2)*($B$3:$E$6))

Hi

Thanks for the reply. It works But when I modified Cell G1 = CY2017-Q1
and the formula
G2=SUMPRODUCT(($B$1:$E$1=mid(G1,3,4))*($B$2:$E$2=G2)*($B$3:$E$6))

It doesn't work.

How to solve this?

Thanks!

 

[Aladin Akyurek]

See post #4 ...

 

[Apple1]

See post #4 ...

=SUM(INDEX(B3:E6,0,MATCH(H1,INDEX(B1:E1,MATCH(I1,$B$2:$E$2,0),0),0)))

doesn't seem to work at all.

 

[Aladin Akyurek]

=SUM(INDEX(B3:E6,0,MATCH(H1,INDEX(B1:E1,MATCH(I1,$B$2:$E$2,0),0),0)))

doesn't seem to work at all.

My bad. It should have been:

=SUM(INDEX($B$3:$E$6,0,MATCH($H1,IF($B$2:$E$2=$I1,$B$1:$E$1),0)))

which needs to be confirmed with control+shift+enter.