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: