=SUM(‘???’!C3) Is it a valid formula?? No. It is magical indeed!

This is about using wildcards in referencing cells on other worksheets.

If you know the basic rule about naming a worksheet, you should know that we cannot use ? or * in any part of a worksheet’s name.  So there won’t be a worksheet named ‘???’ and intuitively you will think the formula set above gives you nothing but error.  Well, it depends… on how you name worksheets in your workbook.

Here’s a typical example of 3D SUM:

The most common way to add the content of C3 from all worksheets should be using mouse to point to the cell reference on different sheets one by one, with the + operator. You can imagine how inefficient it could be.

As the targeted sheets are setting next to each other, there is a more convenient way to do so, as described HERE.

If you prefer to stick to your keyboard, you will love this trick:

=SUM(‘???’!C3) is what I input to the cell

This is what I got:

You may think that keying in the required string “Jan:Dec!” directly, or using mouse to select all the worksheets involved (as described HERE) is equally simple and efficient.  Yes it is when all the targeted worksheets are setting next to each other.

Think about a situation like this:

What you need to do is to add all actual numbers and forecast numbers in a summary table on the worksheet ‘Full Year’.  There is no way for you to input the sheet reference easily.  To tackle this, let’s try the followings:

In C3 of ‘Full Year’, input =SUM(‘???’!C3)

This is what I got:

See! Excel realizes that the sheets are not adjacent to each other and it won’t give you ‘Jan:Dec’! this time.

How about to sum data on C3 from other sheets starting with “f_”?

In D3 of ‘Full Year’, input =SUM(‘f_*’!C3)

This is what I got:

Isn’t it magical?  Excel automatically transforms the short formula I input to what I need.

Here is a Sample File for you to follow the steps above.  (yes, I know how to put an excel file here finally. ) 🙂

The expression

• ‘???’! means any worksheets named with three characters, except active sheet.  It includes worksheet named as ‘ABC’, if any.  ? is the wildcard meaning “Any single character”.
• ‘f_*’! means any worksheets named start with “f_”, except active sheet.   It includes worksheet named as ‘f_ABCDEFG’, if any.  * is the wildcard meaning “Any string of character(s)”.

Notes: When you use wildcard to reference to other sheets, Excel will also look into hidden sheets and included them in your formula.  Please pay attention to this.

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips, Formula and tagged , , . Bookmark the permalink.

31 Responses to =SUM(‘???’!C3) Is it a valid formula?? No. It is magical indeed!

1. Saba says:

Very good and time saving tip. Thanks for enlightening us!!!

Like

• MF says:

You are welcome 😀

Like

2. P says:

Hi thank you so much for sharing such wonderful trick.

I am sorry for bothering you but I have 1 question…

My summary is sheet is in Sheet3.

When I used this trick in Sheet3,
=SUM(‘*’!A1)
it just does =SUM(sheet1:sheet2!A1)

Is there any way to include the cell in the summary sheet too?

Thank you & best regards,

Like

• MF says:

You are welcome!

Like

• MF says:

btw, for your question the way to input the 3D sum described will ignore active sheet.
but you may do a simple twist to achieve what you want:
=SUM(‘‘!A1)+A1
or
=SUM(‘
‘!A1,A1)

Hope this helps.

Liked by 1 person

• P says:

Thank you so much for confirming that it ignores active sheet! I really enjoy reading your blog. It really helps improve my productivity!!!! Thank you so much!!!!!

Like

• MF says:

Glad you like it! I improve my skills a lot in the process too. 😀

Like

3. Ron says:

This is ******* awesome 🙂
I hope Ii wrote enough * :p
Thanks MF

Like

• MF says:

You are welcome, Ron.

Like

4. Chandeep says:

Super Stuff there! I knew wildcards but never thought of using them in the SUM function and on multiple sheets!

Like

• MF says:

Like

5. XOR LX says:

Whoever discovered it, I don’t care! That’s astonishing! 🙂

Like

• MF says:

Hi XOR LX
Nice to hear from you again!
Agree with you
Sharing useful finding could be equally important to the finding itself.
Cheers,

Like

• XOR LX says:

Indeed!

Just a bit of a shame that we can’t generate such constructions via INDIRECT, just as we can’t with constructions such as:

=SUM(Jan:Feb!A1)

I was half hoping we would be able to, but alas no. Still a wonderful find, though!

Cheers

Like

• MF says:

Exactly. The trick is only good for a static workbook. In case extra worksheets are needed in the future, the formula won’t work and we have to be careful about that.
But still, it’s a good hidden feature I believe, although it’s imperfect.
What functions/features are perfect btw. 😛
Cheers,

Like

6. Dennis says:

indeed, very informative…thank you!

Like

• MF says:

Thanks Dennis 😀

Like

7. Anup gupta says:

It’s awesome sir…….. really a great way to do sum….

But i have one query……m suppose we have a combination of worksheets on which some some sheets have 3 character name and some sheets have 4 character name then in such case how to do the sum of all sheets together?????

Thanks for the informative post.
Anup

Like

• MF says:

Thanks Anup for your kind words. For your case, simply use a single * would do.
Hope it help. Cheers 😀

Like

8. Bob Umlas says:

Well, folks, if you got my book, This Isn’t Excel, it’s Magic!, then you’d see this tip #85 (page 166) already “discovered”. The book has 110 other tips & tricks mostly not seen anywhere else! – You can get it by typing “Umlas Magic” into Amazon.com.
Bob Umlas, Excel MVP

Like

• MF says:

Hi Bob,
Cheers,

Like

9. Jim says:

Awesome!
I think I know Excel and then it smacks me this

Like

• MF says:

Thanks 😁

Like

10. Mynda says:

Incredible discovery, MF!

Like

• MF says:

Thank you Mynda indeed! 😀

Like

11. Chandoo says:

Wow… Never knew that. Awesome info.

Liked by 1 person

• MF says:

wow… so glad that Chandoo left a comment here! 🙂

Like

12. XLarium says:

I’m speechless.
+1M

Like

• MF says:

Thanks! 😀

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.