INDIRECT SUMIFS funtions - 3 criteria

psuLemon

New Member
Joined
Aug 10, 2009
Messages
42
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:

LiftVolume% DeltaVolume% Delta
Deadlift34200427220
Bench16650180028

<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:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
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, ...)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top