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>

 

[Apple1]

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.

Hi

Thanks for the reply. But I prefer to use sumproduct. 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]

Hi

Thanks for the reply. But I prefer to use sumproduct. 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!

Using the same sample as before...

H1: 2002
I1: Sales

In J1 enter:

=SUMPRODUCT($B$3:$E$6*(($B$1:$E$1=$H1)*($B$2:$E$2=I1)))

or control+shift+enter, not just enter:

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

 

[Apple1]

Using the same sample as before...

H1: 2002
I1: Sales

In J1 enter:

=SUMPRODUCT($B$3:$E$6*(($B$1:$E$1=$H1)*($B$2:$E$2=I1)))

or control+shift+enter, not just enter:

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

Hi

You are missing the point. I want to extract text in the criteria.
For Eg,
H1: CY2002-Q1

so the formula should be

=SUMPRODUCT($B$3:$E$6*(($B$1:$E$1=mid($H1,3,4))*($B$2:$E$2=I1)))

But it doesn't work.

 

[Aladin Akyurek]

Hi

You are missing the point. I want to extract text in the criteria.
For Eg,
H1: CY2002-Q1

so the formula should be

=SUMPRODUCT($B$3:$E$6*(($B$1:$E$1=mid($H1,3,4))*($B$2:$E$2=I1)))

But it doesn't work.

I advise you to post a sample where CY2002-Q1 occurs...