SUM IF all sheets ending with "Statistics"

[jimmisavage]

I hope I explain this okay because it's quite complicated but here goes:
I have multiple sheets ending with the word "Statistics", Dave Statistics, Stuart Statistics, Alan Statistics etc (more sheets can be added at any given time)

I need a formula that will...
Firstly look in all sheets that end with "Statistics" and check that cell AE1 says "Commercial".
If AE1 does say "Commercial" I need it to add the combined value of Y2:Y1000.
If AE1 doesn't say "Commercial" i need it to ignore that sheet.

Is this even possible?

Kind regards
Jimmi

 

[JackDanIce]

What cell address and sheet name is the formula going into?
Are you going to copy this formula to all sheets ending with "Statistics"?

If so, why can't it just be a single formula for 1 sheet which you then copy+paste as needed?

Or do you mean when AE1 contains "Commercial" (on what sheet?) then the formula to sum range Y2:Y1000 in ALL sheets ending in "Statistics"?

Why is last cell Y1000 and not say Y765 or some other number?

 

[jimmisavage]

Or do you mean when AE1 contains "Commercial" (on what sheet?) then the formula to sum range Y2:Y1000 in ALL sheets ending in "Statistics"?

Why is last cell Y1000 and not say Y765 or some other number?

Hi Jack, that's basically correct. When AE1 an ALL sheets ending in "Statistics" says "Commercial" I would like it to sum Y2:Y1000. It doesn't have to be Y1000 - but i don't see the data going past this point. I guess it could be Y2:Y?

 

[JackDanIce]

Basically correct.. ok try:

=IF(AE1="Commercial",SUM(Y2:Y1000),"")

How does this relate to sheet name where name ends with "Statistics"?

 

[jimmisavage]

Basically correct.. ok try:

=IF(AE1="Commercial",SUM(Y2:Y1000),"")

How does this relate to sheet name where name ends with "Statistics"?

Hi Jack,
The formula will be placed on another sheet named "Home"

Which means your formula might look something like: =IF(Sheet ending in "Statistics"!AE1="Commercial",SUM(Y2:Y1000),"") but obviously that won't work

 

[WaterGypsy]

I think your problem is that the sheets are dynamic .... if you knew all the sheetnames that would apply then writing a formula would be simple .. I think you may well need to use VBA which runs when the sheet is activated to loop through the sheets and check sheet name and the value in AE1 before deciding to accumulate the values in Y

 

[JackDanIce]

What cell address and sheet name is the formula going into?

It will be in sheet "Home" - helpful hint, being precise and explicit saves time, someone reading your post can't see your screen or file. Are you giving them sufficient detailed info for them to imagine the setup you can see?

Easy answer is on sheet Home, create a list of all the worksheet names and next to it, the value of SUM(Y1:Y1000) from that sheet. e.g.

[Sheet Name] abc Ltd =SUM(INDIRECT("'"&A2&"'!Y1:Y1000")) def Ltd =SUM(INDIRECT("'"&A3&"'!Y1:Y1000")) 5 Inc =SUM(INDIRECT("'"&A4&"'!Y1:Y1000")) Then use[code]=SUMPRODUCT(--(RIGHT($A$2:$A$4,LEN("Statistics"))="Statistics"),$B$2:$B$4)[/code]

 

[jimmisavage]

Hi Jack, I wanted to avoid creating a list of sheets - namely because this will change often. Also, this method seems to add each sheet even if T1 doesn't say "Commercial".

I've been doing some research and I can add my sheets inside a ummm, range(?). This works as a sum but i can't seem to make it work as a sumif
=SUMPRODUCT(SUMIF(StatsStart:StatsEnd!T1,"Commercial",StatsStart:StatsEnd!P4))

 

[jimmisavage]

Oh ffs, I'm such an idiot! I didn't even need the IF for what I need to do because it actually doesn't matter if it's 'commercial' or not.

=SUM(StatsStart:StatsEnd!P4) works for my needs. My macro will place the "Statistics" sheets between sheets StatsStart:StatsEnd and this then adds all P4's together.

I appreciate the help. I know i didn't make it easy for you!

 

[JackDanIce]

Argh, if you said VBA/macro at the start (and not formula), it would have been much easier to suggest a solution, similar to what @WaterGypsy suggested!

Anyway, glad you've found some light to aim towards