All articles with 'aggregate()' Tag

Summarize only filtered values using SUBTOTAL & AGGREGATE formulas

Published on Aug 25, 2015 in Learn Excel
Summarize only filtered values using SUBTOTAL & AGGREGATE formulas

We all know the good old SUM() formula. It can sum up values in a range. But what if you want to sum up only filtered values in a range? SUM() doesn’t care if a value is filtered or not. It just sums up the numbers. But there are other formulas that can pay attention […]

Continue »

Calculate sum of top 10 values [formulas + homework]

Published on Aug 4, 2015 in Formula Challenges, Learn Excel
Calculate sum of top 10 values [formulas + homework]

Analyzing top n (or bottom m) items is an important part of any data analysis exercise. In this article, we are going to learn Excel formulas to help you with that.

Let’s say you are the lead analyst at a large retail chain in Ohio, USA. You are looking at the latest sales data for all the 300 stores. You want to calculate the total sales of top 10 stores.  Read on to learn the techniques.

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 »

Calculating Maximum Change [solutions & discussion]

Published on Mar 26, 2014 in Excel Howtos, Formula Forensics
Calculating Maximum Change [solutions & discussion]

Last Friday, we had a fun little Excel challenge – Calculate Maximum Change. More than 170 people commented and shared their solutions to this problem.

And the best part?

The best part is the variety of solutions & thinking displayed by our community. So if you are one of those 170, puff your chest & pat yourself on the back. Go ahead, I will wait.

Today, lets take a look at some of these awesome formulas and understand how they work. Read on and watch the video you below to gain few awesomeness pounds.

Continue »