INDIRECT SUMIFS funtions - 3 criteria

[psuLemon]

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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Lift[/TD]
[TD]Volume[/TD]
[TD]% Delta[/TD]
[TD]Volume[/TD]
[TD]% Delta[/TD]
[/TR]
[TR]
[TD]Deadlift[/TD]
[TD]3420[/TD]
[TD]0[/TD]
[TD]4272[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Bench[/TD]
[TD]1665[/TD]
[TD]0[/TD]
[TD]1800[/TD]
[TD]28[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[BigC]

Steve

I don't understand the layout of your workbook or the data, so don't think I can provide the solution you're looking for. However, from looking at what you've provided, I have to ask why you haven't got all of your data in a table (or tables) on a single sheet? Doing so would make analysis, totalling, etc. much easier than what you're currently trying to do.

 

[psuLemon]

So the lay out of my work book is 6 tabs. A stats tab summarizing my lifts, and 5 tabs (1 tab for each lifting day: Upper Body Strength, Lower Body Strength, Shoulders and Back Hypertrophy, Lower Hypertrophy, and Chest and Arms Hypertrophy. Keeping them separate makes it a lot easier to evaluate the data.


Essentially, the formula I used for the one tab, I would like to apply it to all tabs, which I know can be done with sumproduct/indirect function. I have used indirect with two criteria (on my work computer) but would like to do a three criteria (Lift + volume + cycle (6 weeks) = total)

The below google docs link may give more insight.

https://docs.google.com/spreadsheets/d/14z2bN6M0SmjaBUrtgGiHPDhFDMfnmzKsPXAteuuuw-A/edit?usp=sharing

 

[BigC]

I've only had a brief look at your workbook (where I am in Perth, Western Australia, it's currently midnight on the Friday before Xmas!!), so here's some initial thoughts:

1. Given the 3-D structure of your workbook, I think you're asking for more than the selected Excel functions can deliver! I may be wrong, but I understood that SUMIFS cannot reference 3-D ranges, and also does not work with ranges returned by INDIRECT.

2. The SUMPRODUCT is doing nothing other than summing the result of your SUMIFS function! As SUMPRODUCT is a powerful conditional sum function in its own right (as demonstrated in your first formula), you may be able to get what you want with that function (without SUMIFS) and INDIRECT. However, this will make for a beast of a formula (effectively replicating the SUMPRODUCT formula for each day and adding the results together), so consider ways to use a set of less complex helper/intermediate calculations on each sheet (i.e. to just calc the required results for that sheet) which can then be easily summed via 3-D SUM formula on Stats to give you the desired overall result.

3. To make it easier to build and debug your formulas, until you get them working consider temporarily changing your Sheet names (and the values in your List range) to something simple & short (e.g. Day1, Day2, ...)

 

[psuLemon]

I've only had a brief look at your workbook (where I am in Perth, Western Australia, it's currently midnight on the Friday before Xmas!!), so here's some initial thoughts:

1. Given the 3-D structure of your workbook, I think you're asking for more than the selected Excel functions can deliver! I may be wrong, but I understood that SUMIFS cannot reference 3-D ranges, and also does not work with ranges returned by INDIRECT.

2. The SUMPRODUCT is doing nothing other than summing the result of your SUMIFS function! As SUMPRODUCT is a powerful conditional sum function in its own right (as demonstrated in your first formula), you may be able to get what you want with that function (without SUMIFS) and INDIRECT. However, this will make for a beast of a formula (effectively replicating the SUMPRODUCT formula for each day and adding the results together), so consider ways to use a set of less complex helper/intermediate calculations on each sheet (i.e. to just calc the required results for that sheet) which can then be easily summed via 3-D SUM formula on Stats to give you the desired overall result.

3. To make it easier to build and debug your formulas, until you get them working consider temporarily changing your Sheet names (and the values in your List range) to something simple & short (e.g. Day1, Day2, ...)

Sorry for the late response, as I have been out of town, but thank you for the information.

 

[barry houdini]

Hello Steve,

Your problem is that SUMIFS needs all ranges, sum range and criteria ranges, to be the same dimensions. You have some criteria in rows and some in columns so that isn't the case.

I'd suggest you use your existing SUMPRODUCT function on each worksheet and just total the 5 results in the stats worksheet