Sum across muiltiple sheets only when crieria met

[Phil Payne]

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

 

[XOR LX]

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:

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]

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]

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]

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]

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]

Ah, ok!

You're welcome!

 

[Phil Payne]

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]

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]

P.S. Using absolute referencing (i.e. $) in strings being passed to INDIRECT will not have the desired effect.

Regards