SUMIF, VLOOKUP, or something else?

[cdh121]

Hello, Denizens of the Board:

I have a sheet that contains multiple, non-adjacent columns for "Activity" and multiple, non-adjacent columns for "Reps", with an Activity column always paired with a Reps column...and there are various values in each column, like this:

Day Activity Reps Activity Reps Activity Reps
Mon Walking 20 Sit Ups 50 Curls 10
Tue Pushups 10 Walking 20 Pull Ups 5

I'd like to have a table that will account for, and total, all reps for each given activity, even if a given activity isn't always recorded in the same column (such as the two occurrences of Walking above). The totals don't need to be date-specific in any way; I just want grand totals for each type of activity on the worksheet.

Here's what I know:
1. If a given activity is always recorded in the same column, I can use SUMIF...I just can't guarantee, for example, that Walking will always be placed into Column B, as in the example.
2. If a new activity is done, I need to add a formula to total that activity.
3. SUMIF doesn't seem to work; even though I've named my ranges Activity and Reps, the Names don't seem to want to play nicely with the formula. I'm not savvy enough to know if SUMIFS, SUMPRODUCT, VLOOKUP, or something else might work...that's just a little over my head!

So, I'm looking for something else that will help with this.

Degree of Difficulty: I am using Excel Starter, so don't have access to PivotTables.

Thanks in advance,

cdh121

 

[T. Valko]

Hello, Denizens of the Board:

I have a sheet that contains multiple, non-adjacent columns for "Activity" and multiple, non-adjacent columns for "Reps", with an Activity column always paired with a Reps column...and there are various values in each column, like this:

Day Activity Reps Activity Reps Activity Reps
Mon Walking 20 Sit Ups 50 Curls 10
Tue Pushups 10 Walking 20 Pull Ups 5

I'd like to have a table that will account for, and total, all reps for each given activity, even if a given activity isn't always recorded in the same column (such as the two occurrences of Walking above). The totals don't need to be date-specific in any way; I just want grand totals for each type of activity on the worksheet.

Here's what I know:
1. If a given activity is always recorded in the same column, I can use SUMIF...I just can't guarantee, for example, that Walking will always be placed into Column B, as in the example.
2. If a new activity is done, I need to add a formula to total that activity.
3. SUMIF doesn't seem to work; even though I've named my ranges Activity and Reps, the Names don't seem to want to play nicely with the formula. I'm not savvy enough to know if SUMIFS, SUMPRODUCT, VLOOKUP, or something else might work...that's just a little over my head!

So, I'm looking for something else that will help with this.

Degree of Difficulty: I am using Excel Starter, so don't have access to PivotTables.

Thanks in advance,

cdh121

Try this...

Data table:

Book1
	B	C	D	E	F	G
1	Activity	Reps	Activity	Reps	Activity	Reps
2	Walking	20	Sit Ups	50	Curls	10
3	Pushups	10	Walking	20	Pull Ups	5
Sheet1

Summary table:

Book1
	B	C
8	Walking	40
9	Sit Ups	50
10	Pushups	10
11	Pull Ups	5
12	Curls	10
Sheet1

This formula entered in C8 and copied down:

=SUMIF(B$2:F$3,B8,C$2:G$3)

Note how the ranges are offset.

 

[cdh121]

Try this...

Data table:

Book1
*	B	C	D	E	F	G
1	Activity	Reps	Activity	Reps	Activity	Reps
2	Walking	20	Sit Ups	50	Curls	10
3	Pushups	10	Walking	20	Pull Ups	5
Sheet1

Summary table:

Book1
*	B	C
8	Walking	40
9	Sit Ups	50
10	Pushups	10
11	Pull Ups	5
12	Curls	10
Sheet1

This formula entered in C8 and copied down:

=SUMIF(B$2:F$3,B8,C$2:G$3)

Note how the ranges are offset.

That seems to have worked nicely...I think the offset range may have been the piece that I was missing. Thanks so much for your excellent, timely help!

 

[T. Valko]

That seems to have worked nicely...I think the offset range may have been the piece that I was missing. Thanks so much for your excellent, timely help!

You're welcome. Thanks for the feedback!