Login to online classes

Tag: sumif()

Introduction to Excel SUMIFS Formula

sumifs - advanced excel formula

Excel SUMIFS function is used to calculate the sum of values that meet any criteria. For example, you can calculate the total sales in east zone for product Pod Gun using SUMIFS formula.
In this article, you will learn:

  • What is SUMIFS function and how to use it?
  • Syntax for SUMIFS
  • Using SUMIFS() with tables and structural references
  • SUMIFS examples – simple, wild card
  • Using SUMIFS() with date & time values
  • Free sample file for SUMIFS formula
  • More formulas for data analysis

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

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

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.

Highlight best week & month in a trend chart [tutorials]

When analyzing business data like sales, shop visits or productivity, one of the questions managers always ask is,

What is the best month / week for this data?

To answer this question, we need to make a chart that looks like above. Today, lets learn how to highlight portions of such charts that correspond to best week or best month.

Comparing Lists of Values in Excel using Array Formulas

Last week, we had a home work on Calculating Donation Summaries using Excel Formulas. This is a good case where array formulas can help us. So today, we will learn how we can use Array Formulas to compare lists of values and calculate summaries. Towards the end of this post, you can see a video that explains the solution to Donation Summary Calculation problem.

Use Copy & Paste to Preserve References to Tables [Quick Tip]

With Excel 2007, Microsoft has introduced a powerful and useful feature called as Tables. One of the advantages of Tables is that you can write legible formulas by using structural references. That means, you can write easy to understand formulas like this,

But, there is a problem. When you write these formula and drag the formula cell sideways to fill remaining cells, Excel changes table column references and thus makes your formulas almost useless.

Well, there is a simple workaround for this problem

6 Tips for Writing Better VLOOKUPs

Ok, you have learned how to write vlookup formulas. You have also seen some pretty interesting examples of it (1, 2).

But how do you write better VLOOKUP formulas?

Here is a list of 6 tips that work wonders with VLOOKUP writing.