Hello everyone. I figured I would ask a question because I am going crazy figuring out where I am going wrong. Basically, I am pulling together a workout to look at the total volume for moves for my lifting program. I have a 5 day lifting routine, so ideally, I would like to simplify the below working formula to be tab agnostic. Below was my initial formula which worked when I applied it to one tab.

SUMPRODUCT(--('Day 1'!$G$36:$AF$45),('Day 1'!$C$36:$C$45=Stats!$B3)*('Day 1'!$G$35:$AF$35="Volume")*('Day 1'!$G$33:$AF$33=Stats!C$2))

Essentially, I have three criteria that I am "searching" on:

1. Lift (Bench, DL, Squat) - C36:C45 on each tab

2. Cycle (Every 6 weeks is a cycle) - I have a hidden label in cells G33:AF33 on each tab

3. Looking for the word volume

So to provide an example of what my sheet will look like, below is an example for 1 cycle:

<tbody>

</tbody>

TLDR: I would like to sum the total volume, for each move, for each cycle. Below is the starting formula that keeps providing a "#value!"

List = the 5 tab names (day 1, day 2, day 3, day 4, day 5).

=SUMPRODUCT(SUMIFS(INDIRECT("'"&List&"'!$G$36:$AF$45"),INDIRECT("'"&List&"'!$C$36:$C$45"),$B4,INDIRECT("'"&List&"'!$G$35:$AF$35"),"*Volume*",INDIRECT("'"&List&"'!$G$33:$AF$33"),C$2))

Hopefully this all makes sense.

Thanks,

Steve

SUMPRODUCT(--('Day 1'!$G$36:$AF$45),('Day 1'!$C$36:$C$45=Stats!$B3)*('Day 1'!$G$35:$AF$35="Volume")*('Day 1'!$G$33:$AF$33=Stats!C$2))

Essentially, I have three criteria that I am "searching" on:

1. Lift (Bench, DL, Squat) - C36:C45 on each tab

2. Cycle (Every 6 weeks is a cycle) - I have a hidden label in cells G33:AF33 on each tab

3. Looking for the word volume

So to provide an example of what my sheet will look like, below is an example for 1 cycle:

Lift | Volume | % Delta | Volume | % Delta |

Deadlift | 3420 | 0 | 4272 | 20 |

Bench | 1665 | 0 | 1800 | 28 |

<tbody>

</tbody>

TLDR: I would like to sum the total volume, for each move, for each cycle. Below is the starting formula that keeps providing a "#value!"

List = the 5 tab names (day 1, day 2, day 3, day 4, day 5).

=SUMPRODUCT(SUMIFS(INDIRECT("'"&List&"'!$G$36:$AF$45"),INDIRECT("'"&List&"'!$C$36:$C$45"),$B4,INDIRECT("'"&List&"'!$G$35:$AF$35"),"*Volume*",INDIRECT("'"&List&"'!$G$33:$AF$33"),C$2))

Hopefully this all makes sense.

Thanks,

Steve

Last edited: