Excel Tricks that will let you stand out from the crowd – Part Two

AD: Earn money by doing online surveys!!!

So last week I looked at the VLOOKUP function with it’s limitations, and gave insight on how to use the INDEX and MATCH functions together to create powerful queries.

Today I am going to discuss the 3D SUM function.

The 3D SUM function basically gives you a nice overview or summary from a group of tables or sheets.

Here’s an example to explain:

I want to track my spending for 5 weeks and see where my money goes.

3dsum1

So on my OVERVIEW sheet, I create a similar table, but now we use the 3D SUM formula as follows:

=SUM(‘Week One:Week Five’!B2)

3dsum2

Using the above formula, you are saying:

Give me the SUM of all values in cell B2, between sheet named WEEK ONE and sheet named WEEK FIVE.

The result in this case is $85.00 spent on Mondays for food.

3dsum3

So, now I can apply the same formula to all the other cells:

3dsum4

Looking at the above, I now know that I spent the most over weekends, and I can also see totals showing what I spent my money on.

The 3D SUM function is pretty powerful, and works well if you have a bunch of spreadsheets with the same layout and format.

101 EXCEL SECRETS!!!

Well, that’s it! Short and sweet, but hopefully useful.

Let me know your thoughts and suggestions!

 

Thanks to Business Insider for the great lesson!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s