Sum across muiltiple sheets only when crieria met

Phil Payne

Board Regular
Joined
May 17, 2013
Messages
89
Hello all,

I've tried if, sumif, sumproduct, indirect etc and cannot find a way to do this i hope someone can help me.

My Summary sheet contains a cell with the following formula =SUM(Abe:Wyn!I7) (where Abe:Wyn = 218 worksheets and identically formatted)

This has worked well but now I need to ignore certain of the sheets between Abe and Wyn and cannot remove or reorder them.

My chosen way of doing this was to look for the contents of a particular cell on my Summary sheet in the same cell on each of the 218 sheets and if found sum values of other cells (I7) to total.

I want to =SUM(Abe:Wyn!I7) but only if Abe:Wyn cells C7 equals C7 on my summary sheet.

Can anyone help please?

Thanks
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
Hi.

If you first go to Name Manager and define:

Name: Sheets
Refers to: ={"Abe","Bev","Cal","Dan","Eve","Wyn"}

(Or whatever happen to be the sheet names in question.)

Your formula is then:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!C7"),Summary!C7,INDIRECT("'"&Sheets&"'!I7")))

If you don't already have a list of all your sheet names somewhere, it might save a lot of manual effort to run this small piece of code, which will list those names beginning in cell A1 of the worksheet Summary:

Code:
Sub GetSheetNames()


Dim ws As Worksheet


i = 1


For Each ws In Worksheets
    
    If ws.Index >= Sheets("Abe").Index And ws.Index <= Sheets("Wyn").Index Then
        Worksheets("Summary").Range("A" & i).Value = ws.Name
    i = i + 1
    End If


Next ws


End Sub

Regards
 

Phil Payne

Board Regular
Joined
May 17, 2013
Messages
89
Hello XOR

Thanks very much for your prompt response. The code to list all 218 worksheets is very helpful however creating the named range 'Sheets' is proving problemattic. The formula is accepted in the cell but it does not return a value.

Ive noticed that the formula shown in the Name Manager has semi colon instead of comma separators.
It shows {"Abe";"Bev";"Cal";"Dan";"Eve";"Wyn"} and not {"Abe","Bev","Cal","Dan","Eve","Wyn"} would this be the cause?

I'm double checking my work to ensure I haven't made a simple mistake.

Regards,
Phil
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
It does not matter in this case if the list of sheets is separated by commas or by semi-colons.

And apologies - I forgot to tell you a very simple trick to generate that delimited list for Name Manager from the worksheet range. Hope you didn't suffer too much!

So what exactly is the issue? What do you mean by "does not return a value"? What does the formula return? An error? If so, which one?

Regards
 

Phil Payne

Board Regular
Joined
May 17, 2013
Messages
89

ADVERTISEMENT

There is no error shown, the formula is sound - it just returns nothing '0' where I know there should be a value.

The fields being summed are themselves calculated fields:

"=IF(ISNA(VLOOKUP($C$2,ThemeRange,MATCH($H8,xRefurbPricingSheet!$A$14:$EX$14,0),FALSE)),"",(VLOOKUP($C$2,ThemeRange,MATCH($H8,xRefurbPricingSheet!$A$14:$EX$14,0),FALSE)))"

Could this be the problem?
 

Phil Payne

Board Regular
Joined
May 17, 2013
Messages
89
Hello XOR.

It was me:(

Your solution works I had just referrd to the wrong column.


Doh!


Thanks very much for your help.
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517

ADVERTISEMENT

Ah, ok!

You're welcome!
 

Phil Payne

Board Regular
Joined
May 17, 2013
Messages
89
Hello again XOR,

I hope a follow up questions is in order.

The formula you provided worked exactly as required however a discrepancy in the total returned made me realise I need to take another criteria into account.

There is a column in each sheet containing statuses from 1 to 11 and I need to sum only those with status 7, 8, 9 or 11.

I've attempted to modify the formula and my latest attempt comes up with a #Value! error.

Hopefully you can see what im trying to do from my failed attempt:
=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!$D11"),ThemeSummary!$C11,(INDIRECT("'"&Sheets&"'!$F11"),IF(F11,{"7","8","9","11"}),INDIRECT("'"&Sheets&"'!$I11"))))

Can you see a way to make this work?

Thanks for your time.
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
Hi again.

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!$I11"),INDIRECT("'"&Sheets&"'!$D11"),ThemeSummary!$C11,INDIRECT("'"&Sheets&"'!$F11"),{7;8;9;11}))

Note that this second array constant ({7;8;9;11}) must be orthogonal to that which you defined as Sheets. To put it in layman's terms, if the array defined as Sheets is comma-separated, this second array constant should be semicolon-separated, and vice versa.

Regards
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
P.S. Using absolute referencing (i.e. $) in strings being passed to INDIRECT will not have the desired effect.

Regards
 

Watch MrExcel Video

Forum statistics

Threads
1,123,176
Messages
5,600,154
Members
414,367
Latest member
dw970906

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
Top