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]

 

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