• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sum over variable named tabs

M

markhimself

New Member
I've tried without success to sum a specific cell over multiple tabs (cell C7 for instance) while trying to keep the names of the tabs variable. For instance, if I have months on separate tabs and I want to "roll up" the values for Quarter 1 (Jan - Mar), YTD (Jan - Dec), etc. it would require summing the same cell over a variety of tabs names.

Code: 
 =SUMPRODUCT(SUMIF(INDIRECT("'" & $D$6 & ":" & $E$6 & "'!" & D$1 & $A8),"<>0"))

In column A I have the row number labeled so that it can dynamically change as I am wanting to copy the formula so that it will sum the cell it is in (ie sum more than just cell C7, but also C8 or D7). Row 1 has the column (A, B, C, etc.) so that it can dynamically change and add those values.

Is there any way to do this without VBA?
 
This is a simplified example. I can write the formula if I want to hard code the range. If the sheet range is to be dynamic, that's where I'm having trouble. In this case the answer is 7 but I can't get a dynamic reference to make it work.
 

Attachments

Where could I change it from summing all tabs to only a select group of tabs? Say it was meaningful to sum only Jan:Feb or Jan:Apr?
 
I'm getting an #N/A error after trying to mirror the named ranges and formula on the document you uploaded. Any idea what the problem might be?
 
@markhimself

Not sure, I think there is some problem with ref. in the formula do check them and #N/A seems to be getting generated from MATCH function. Just check them by copying the formula in cell and doing Evaluate formula from formula tab or using shortcut Alt+T+U+F.

Regards,
 
You must log in or register to reply here.
Back
Top