All articles with 'sumifs' Tag

Matching transactions using formulas [Accounting]

Published on Jun 6, 2014 in Excel Howtos, Learn Excel
Matching transactions using formulas [Accounting]

Imagine you are the head of Accounts Receivable department at a large company. Drab, I know, But humor me and imagine.

Now, every month you get a transaction report.

And you want to know which numbers are matching up.

i.e, if your company gave Vendor-0002 $872.34 on 1st of April, 2014 and your received below payments from them subsequently,

  • $427.77 on 1st April
  • $152.88 on 2nd April
  • $291.69 on 2nd April

Then you consider the account matched since the total received is same as total payable.(427.77 + 152.88 + 291.69 = 872.34).

Continue »

CP007: aweSUM() – Overview of SUM functions in Excel

Published on May 1, 2014 in Chandoo.org Podcast Sessions, Learn Excel
CP007: aweSUM() – Overview of SUM functions in Excel

In the 7th session of Chandoo.org podcast, lets make you aweSUM().

Imagine for a second that Excel cannot add up numbers. And no it cant subtract them either. What would that look like?

A glorified Notepad. That’s right. Excel’s ability to add up numbers, along with features like formulas, charts, pivot tables & BHATTEXT() are what make it such a lovely software. May be not the BHATTEXT(), but we all agree that Excel is so versatile and useful because it can add up numbers (and perform other calculations) with ease.

But how well do you know the SUM formulas of Excel?

In this podcast, you will learn,

  • Special personal fruit announcement 😛
  • + operator
  • Status bar & total rows in tables
  • Auto Sum feature
  • SUM() function
  • SUMIFS function
  • Special cases of SUMIFS function
  • SUBTOTAL & AGGREGATE functions
  • Other summing functions – SUMPRODUCT etc.
Continue »

Introduction to Structural References

Published on Jun 26, 2013 in Learn Excel

Ever seen a formula like =SUMIFS(Sheet1!B2:B3923, Sheet1!C2:C3923, A1, Sheet1!D2:D3923, A2) and wondered what it is really doing?!?

If so, you are not alone.

Formulas written with cell references tend to look complicated and clunky. What if we could write formulas in plain English?

That is what Structural References do. When using structural references in formulas, your focus will be on your data, not on which cell ranges the data takes up.

For example, you can write formulas like these:
1) SUM(mySales[no. of customers]) to find how many customers we had.
2) SUMIFS(mySales[no. of customers], mySales[product], “FastCar”) to find how many customers bought “FastCar”

Continue »

How well do you know your LOOKUPs? – Quiz Answers & Discussion

Published on Apr 4, 2013 in Excel Howtos, Learn Excel, Quizzes
How well do you know your LOOKUPs? – Quiz Answers & Discussion

Last week, we had our very first quiz – “How well do you know your LOOKUPs?”. I hope you have enjoyed it.

Today lets understand the answers & explanations for this quiz.

Continue »

Details about our Power Pivot Course [and a video for those of you not interested]

Published on Jan 30, 2013 in Excel Howtos, Power Pivot
Details about our Power Pivot Course [and a video for those of you not interested]

Hello folks,

If this article was a person, they would be schizophrenic. You see, it has 2 purposes:

  • Give you all the details about my upcoming Power Pivot course
  • Give you a solution to last week’s vacation days problem

Details about Power Pivot Course
Power Pivot, an Excel add-in makes it easy to connect, analyze & visualize massive amounts of data. This course aims to teach you how to use Power Pivot to analyze data, create advanced reports & prepare dashboards all from familiar interface of Excel. This is ideal for data analysts, reporting & MIS professionals, business analysts, managers & dashboard makers.

Continue »

Designing a dashboard to track Employee vacations [case study]

Published on Jan 24, 2013 in Charts and Graphs, Learn Excel

Employee Vacation Dashboard & Tracker using Excel

HR managers & department heads always ask, “So what is the vacation pattern of our employees? What is our average absent rate?”

Today lets tackle that question and learn how to create a dashboard to monitor employee vacations.

What do HR Managers need? (end user needs)

There are 2 aspects tracking vacations.

1. Data entry for vacations taken by employees
2. Status dashboard to summarize vacation data

Continue »

Top 10 Formulas for Aspiring Analysts

Published on Jan 16, 2013 in Learn Excel
Top 10 Formulas for Aspiring Analysts

Few weeks ago, someone asked me “What are the top 10 formulas?” That got me thinking.

While each of us have our own list of favorite, most frequently used formulas, there is no standard list of top 10 formulas for everyone. So, today let me attempt that.

If you want to become a data or business analyst then you must develop good understanding of Excel formulas & become fluent in them.

A good analyst should be familiar with below 10 formulas to begin with.

Continue »

Making your dashboards interactive [Dashboard Essentials]

Published on Aug 2, 2012 in Charts and Graphs, Excel Howtos, Learn Excel
Making your dashboards interactive [Dashboard Essentials]

Everyone likes to be in control. Even my 2 year old daughter jumps with joy when she lays her hands on TV remote. She pushes the buttons and assumes it is working. It is another story that we rarely watch TV at home.

By adding an element of control, we can make our dashboard reports fun. Interactive elements like form controls, slicers etc. invite users to play with your dashboard, get involved and understand data by asking questions. That is why I recommend making dashboards interactive.

Today lets understand how you can make dashboards interactive.

Continue »

Interactive Sales Chart using MS Excel [video]

Interactive Sales Chart using MS Excel [video]

Finally, I got some time to sit down and do what I love most – write a blog post to make you awesome in Excel. After a whirlwind trip to Sydney, I am back in India to spend few days with my kids & wife before rushing to Australia to run 2nd leg of my training programs (in Perth, Melbourne & Brisbane). I did 2 sessions in Sydney – one for KPMG and other for public and both went very well. We got lots of positive feedback and people really loved it. I am saving the details for another post, but today lets talk about Interactive Sales Chart using Excel.

Take a look at the Interactive Sales Chart

First, take a look at interactive sales chart. Today, you will learn how to build this using Excel.

Continue »

Comprehensive Guide to VLOOKUP & Other Lookup Formulas

Published on Mar 30, 2012 in Learn Excel
Comprehensive Guide to VLOOKUP & Other Lookup Formulas

This week many Excel bloggers are celebrating VLOOKUP week. So I wanted to chip in and give you a comprehensive guide to VLOOKUP & Other lookup formulas. Read on …,

What is VLOOKUP Formula & how to use it?

I often tell my excel school students that learning VLOOKUP formulas will change your basic approach towards data. You will suddenly feel that you have discovered a superman cape in your attic. It is that awesome.

Continue »

Speed up your Excel Formulas [Speedy Spreadsheet Week]

Published on Mar 20, 2012 in Excel Howtos, Learn Excel
Speed up your Excel Formulas [Speedy Spreadsheet Week]

Excel formulas acting slow? As part of our Speedy Spreadsheet Week, today lets talk about optimizing & speeding up Excel formulas. Use these tips & ideas to super-charge your sluggish workbook. Use the best practices & formula guidelines described in this post to optimize your complex worksheet models & make them faster.

1. Use tables to hold the data
2. Use named ranges & named formulas
3. Use pivot tables
4. Sort your data
5. Use manual calculation mode

… and more. Read on to learn these top 10 tips & ideas to improve performance of your excel formulas.

Continue »

Sum of Values Between 2 Dates [Excel Formulas]

Published on Sep 27, 2011 in Excel Howtos, Learn Excel
Sum of Values Between 2 Dates [Excel Formulas]

Lets just say, you run a nice little orange shop called, “Joe’s Awesome Oranges“. And being an Excel buff, you record the daily sales in to a workbook, in this format. After recording the sales for a couple of months, you got a refreshing idea, why not analyze the sales between any given 2 dates? […]

Continue »

Comparing Sales of One Product with Another [Excel Techniques]

Published on Apr 25, 2011 in Charts and Graphs
Comparing Sales of One Product with Another [Excel Techniques]

This is a guest article by Theodor on how to Compare Sales of One Product with Another

Ok, now here’s one for you.

Suppose you’d like to come up with a sales report on different products, comparing their evolution on the same period of different years (say Jan ’09 vs. Jan Jan ’10). At the same time, you’d like to keep an eye on their yearly trend (entire 2009 vs. entire 2010).

Read on to learn how you can use Excel Charts to do this very easily.

Continue »

Exclude Hidden Rows from Totals [How to?]

Published on May 11, 2010 in Excel Howtos, Learn Excel
Exclude Hidden Rows from Totals [How to?]

Denice, an Excel School student emailed me an interesting problem. I have a bunch of data from which I want to find the sum of values that meet a criteria. But I also want to exclude any rows that are hidden. Well, we know how to find sum of values that meet a criteria – […]

Continue »

SUMIF works in 2D too [quick tip]

Published on Apr 27, 2010 in Learn Excel
SUMIF works in 2D too [quick tip]

We all know that SUMIF formula can be used to find the sum of values meeting a criteria. Like this, But I was pleasantly surprised to realize that SUMIF works equally well for 2D ranges too, like this: During a recent consulting work with a client I had a requirement to sum up values that […]

Continue »