If Cells Contain (Specific) Text Multiply Corresponding Cells (Excel-Meathead needs help)

[Cederstark]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD](A) Exercise[/TD]
[TD](A) Sets[/TD]
[TD](A) Reps[/TD]
[TD](B) Total[/TD]
[TD](B) Sets[/TD]
[TD](B) Reps[/TD]
[/TR]
[TR]
[TD]Squat[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]Squat[/TD]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Bench[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]Bench[/TD]
[TD]2[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Deadlift[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]Deadlift[/TD]
[TD]3[/TD]
[TD]15[/TD]
[/TR]
</tbody>[/TABLE]

Hey everyone, I'm using Excel 2016, and I'm trying to have Excel auto-calculate the total number of reps based on the exercise. I've managed auto-calculating the total number of sets using the "SUMIF"-command, however, I'm at a loss when it comes to this portion.

Calculating reps is very straightforward (Sets x Reps = Total Reps).

In it's simplest terms I'm trying to have Excel do the following:
1. Identify the exercise under (A) Exercise.
2. Multiply (A) Sets with (A) Reps.
3. Show the result in (B) Reps, next to the correct exercise.

Hopefully this is legible enough to warrant a response. Thanks to everyone in advance!

 

[jtakw]

Hi,

Here's one way:

Excel 2010
	A	B	C	D	E	F
1	(A) Exercise	(A) Sets	(A) Reps	(B) Total	(B) Sets	(B) Reps
2	Squat	1	5	Squat	1	5
3	Bench	2	5	Bench	2	10
4	Deadlift	3	5	Deadlift	3	15
Sheet1

Cell Formulas
Range	Formula
F2	=SUMPRODUCT((A$2:A$4=D2)*(B$2:B$4)*(C$2:C$4))

F2 formula copied down.

 

[jtakw]

On second thought, maybe you meant this:

Excel 2010
	A	B	C	D	E	F
1	(A) Exercise	(A) Sets	(A) Reps	(B) Total	(B) Sets	(B) Reps
2	Squat	1	5	Squat	2	10
3	Bench	2	5	Bench	3	15
4	Deadlift	3	5	Deadlift	4	20
Sheet1

Cell Formulas
Range	Formula
F2	=SUMPRODUCT((A$2:A$4=D2)*(E$2:E$4)*(C$2:C$4))

 

[Cederstark]

This solved it, thank you so much!

 

[jtakw]

You're welcome, welcome to the forum.