# Summing same cell where work sheet contains...

#### menor59

##### Well-known Member
I have the following formula:

Code:
``='Quote 1'!\$D65+'Quote 3'!\$D65+'Quote 2'!\$D65+'Quote 4'!\$D65+'Quote 5'!\$D65``

Is there a way to have it do Something along the lines of
Code:
``=SUM(Quote*\$d65)``

basically Look at any sheet that is named Quote and sum those Cells up?

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

#### AlphaFrog

##### MrExcel MVP
You could try this...

=SUM('Quote 1:Quote 5'!D65)

Note:it will sum all sheets in between Quote 1 and Quote 5 inclusive regardless of their sheet names. It is positional and not name based.

#### menor59

##### Well-known Member
I thought of that...but theoratically others could have Quote 6, 7 etc.etc...I need to Capture Quote * or a variant there of...thoughts?

#### AlphaFrog

##### MrExcel MVP
Could you have a dummy "End Sheet" and have all your quote sheets in between?
=SUM('Quote 1:End Sheet'!D65)

Or it could be done with VBA code if you're up for that?

#### menor59

##### Well-known Member
Alpha,

Thanx, That wont work either...

I have 12 sheets labeled...

Quote 1, Quote 2, Quote 3,Quote 4, Quote 5, CO 1, CO 2, CO 3, CO 4, CO 5, Turnover, Totals, DataQuote, DataCO.

So for This discussion...I was hoping to Get the Formula to work for Quote*, then i would have put in in a different cell to Look at only CO*. I hate to say it but it needs to be wildcarded....\

Thoughts?

#### AlphaFrog

##### MrExcel MVP
There's probably a clever way to do it with just a formula but it's beyond me.

Here's a custom UDF function. Put the code in a standard macro code module. Then enter a formula like this
=SUMQUOTES(D65)

Code:
``````[COLOR=darkblue]Function[/COLOR] SUMQUOTES(Rng [COLOR=darkblue]As[/COLOR] Range) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Single[/COLOR]
[COLOR=darkblue]Dim[/COLOR] ws As Worksheet
Application.Volatile
[COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ws [COLOR=darkblue]In[/COLOR] Worksheets
[COLOR=darkblue]If[/COLOR] LCase(ws.Name) [COLOR=darkblue]Like[/COLOR] "quote*" [COLOR=darkblue]Then[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
[COLOR=darkblue]Next[/COLOR] ws
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]``````

Thank You Sir!

#### AlphaFrog

##### MrExcel MVP
This will allow you to specify a base sheet name with wildcard.
=SUMSHEETS("Quote*",D65)

Code:
``````[COLOR=darkblue]Function[/COLOR] SUMSHEETS(strBase [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], Rng [COLOR=darkblue]As[/COLOR] Range) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Single[/COLOR]
[COLOR=darkblue]Dim[/COLOR] ws As Worksheet
Application.Volatile
[COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ws [COLOR=darkblue]In[/COLOR] Worksheets
[COLOR=darkblue]If[/COLOR] ws.Name [COLOR=darkblue]Like[/COLOR] strBase [COLOR=darkblue]Then[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
[COLOR=darkblue]Next[/COLOR] ws
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]``````

Last edited:

#### menor59

##### Well-known Member
Wasnt the One previous the same thing?

#### AlphaFrog

##### MrExcel MVP
No. SUMQUOTES only summed the "Quote*" sheets. The base sheet name was fixed in the code.
=SUMQUOTES(D65)

The SUMSHEETS function allows you to set the base name in the formula. So if you wanted to sum the CO sheets, then...
=SUMSHEETS("CO*",D65)

Replies
4
Views
160
Replies
12
Views
302
Replies
2
Views
248
Replies
35
Views
914
Replies
16
Views
256

1,195,591
Messages
6,010,614
Members
441,558
Latest member
lambierules

### 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.

### Which adblocker are you using?

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

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