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!

	2001	2001	2002	2002
	Sales	Profit	Sales	Profit
James	1000	690	8559	1895
Peter	2000	756	4560	1596
Tom	3000	56	1520	3698
Edwin	4000	56	1563	4563

<tbody>
</tbody>

 

[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]

	A​	B​	C​	D​	E​	F​	G​
1​		2001​	2001​	2002​	2002​		2002​
2​		Sales	Profit	Sales	Profit		Sales
3​	James	1000​	690​	8559​	1895​		16202​
4​	Peter	2000​	756​	4560​	1596​
5​	Tom	3000​	56​	1520​	3698​
6​	Edwin	4000​	56​	1563​	4563​

<tbody>
</tbody>

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]

	A​	B​	C​	D​	E​	F​	G​
1​		2001​	2001​	2002​	2002​		2002​
2​		Sales	Profit	Sales	Profit		Sales
3​	James	1000​	690​	8559​	1895​		16202​
4​	Peter	2000​	756​	4560​	1596​
5​	Tom	3000​	56​	1520​	3698​
6​	Edwin	4000​	56​	1563​	4563​

<tbody>
</tbody>

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]

	A​	B​	C​	D​	E​	F​	G​
1​		2001​	2001​	2002​	2002​		2002​
2​		Sales	Profit	Sales	Profit		Sales
3​	James	1000​	690​	8559​	1895​		16202​
4​	Peter	2000​	756​	4560​	1596​
5​	Tom	3000​	56​	1520​	3698​
6​	Edwin	4000​	56​	1563​	4563​

<tbody>
</tbody>

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.