Search

# All articles with 'sumifs' Tag

## Speed up your Excel Formulas [10 Practical Tips]

Published on Apr 14, 2023 in Excel Howtos, Learn Excel

Excel formulas acting slow? 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 Dynamic Array formulas
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 »

## A clever technique to simplify your long, nested IF formulas

Published on Apr 11, 2023 in Formula Forensics, Learn Excel

Recently I used an elegant and smart technique to simplify lengthy nested IF formulas and made them 80% shorter. In this article, let me explain the process and share the results.

Continue »

## Which Excel Formulas should you learn first?

Published on Nov 14, 2022 in Learn Excel

Let’s face it. You already know how to use SUMs, COUNTs and IFs and want to level up. But there are 100s of functions in Excel and you are not sure which ones to focus on.

In this article, let me reveal the top 10 important functions in Excel for data analysis work.

Continue »

## Excel formula to convert calendar format to table

Published on Sep 3, 2020 in Learn Excel

Got some data in a calendar format and just wished you can get it in tabular format?

You can use Excel formulas or Power Query to do this. In this article, let’s review formula based approach with two excellent choices.

Continue »

## Top 10 Excel formulas for IT people

Published on Jun 18, 2020 in Learn Excel, Project Management

Are you in IT & use Excel often? This article explains top 10 formulas for IT professionals. Useful for project managers, IT analysts, Testing people and BAs.

We cover a 10 practical situations and explore various Excel formulas to solve them. Example workbook provides more details too.

Continue »

## Top 10 Excel Formulas for any situation

Published on Mar 27, 2019 in Learn Excel

Excel has hundreds of formulas. But as a new learner or user, you may want to just focus on top 10 formulas to get the most out of it. Assuming you already know the basics (check out Beginner Excel page if you are complete newbie), here is a list of top 10 Excel formulas for you.

Continue »

## Introduction to Excel SUMIFS Formula

Published on May 25, 2018 in Learn Excel

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
Continue »

## Stay on top of money with this awesome household budget spreadsheet [downloads]

Published on Mar 1, 2018 in Learn Excel, Templates

I believe in frugal living and paying yourself first. One of the simple ways to achieve this is by using a budget. You know how much money you get. Once you can track (or estimate) how much you are spending, it is easy to see how much you are paying your future self and what wiggle room you have. So in the spirit of making you awesome in life, not just Excel, let me share a simple but elegant household budget spreadsheet.
Here is a screenshot of the budget.

Continue »

## Road Trip Planner Template [Excel Downloads]

Published on Feb 1, 2017 in Learn Excel

We recently went on a road trip around parts of New Zealand’s north island. We have been to Taupo, Rotorua, East Cape and Napier. It took us 2 weeks, we drove more than 2,000 km and spent almost NZ \$3,000 on the trip. Of course, being a data nerd, I made a plan of the trip in Excel and that helped us budget for this.

After getting back to home, I thought it would be fun to polish the planner workbook and share it with you all so you too can plan a fabulous road trip. So here we go.

Continue »

## Sum up neither “A” nor “B” values – How to use DSUM function in Excel [video]

Published on Jun 8, 2016 in Excel Howtos, Learn Excel

We know how to use SUMIFS function to answer questions like, “What is the sum of values for ‘A’?”  But how would you answer questions like,

• What is the sum of values that are neither “A” nor “B”?

We can still use SUMIFS, but it will get awfully long. So let’s turn our attention to other functions in Excel.

Continue »

## Analyzing half a million customer complaints – Regional Trends [Part 2 of 3]

This is part two of our three part series on how to analyze half a million customer complaints. Read part 1 here.

## Analyzing Regional Trends

As introduced in part 1, our complaints dataset has geographical information too. We know the state & zip code for each complaint. Please note that zip codes are partial or missing for a 10% of the data.

In this article, let’s explore three ways to analyze regional trends.

1. Regional trends by state, product & issue
2. Complaints per million by state
3. Complaints by zip code
Continue »

## Formula Forensics 040 – Apportioning Sales by Criteria

Published on Feb 15, 2016 in Excel Howtos, Formula Forensics, Huis, Posts by Hui

Lets look at how to apportion sales according to multiple criteria

Continue »

## Pricing Tier Lookup formula

Published on Dec 1, 2015 in Excel Howtos, Learn Excel

Here is an interesting twist on the good old VLOOKUP. How to find the pricing applicable for given quantity of a product?

Something like above.

Looks interesting? Then read on…

Continue »

## Employee training tracker & calendar – tutorial & download

Published on Nov 4, 2015 in Charts and Graphs, Learn Excel

Imagine you are the head of training department at ACME Inc. You arrange training programs round the year to empower your team. It is hard work, coordinating between employees, trainers, department heads, venues and coffee machines. What if there is something to help you keep track of all this? I am not talking about getting you a shiny new iPad, you silly. I am talking about a tracker & calendar built in Excel that ties everything together (well, almost everything, you still have to fill the coffee machine.)

We are going to build a training program tracker & calendar using Excel.

Continue »

## CP030: Detecting fraud in data using Excel – 5 techniques for you

Published on Feb 19, 2015 in Analytics, Chandoo.org Podcast Sessions

In the 30th session of Chandoo.org podcast, let’s learn how to uncover fraud in data.

### What is in this session?

In the wake of hedge fund scams, accounting frauds and globalization, We, analysts are constantly second guessing every source of data. So how do you answer a simple question like, “am I being lied to?” while looking at a set of numbers your supplier has sent you.

That is our topic for this podcast session.

In this podcast, you will learn

• Quick announcements about 50 ways & 200k BRM
• Introduction to fraud detection
• 5 techniques for detecting fraud
• Benford’s law
• Auto correlation
• Discontinuity at zero
• Analysis of distribution
• Learning systems & decision trees
• Implementing these techniques in Excel
• A word of caution
Continue »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.

#### Downloads

© All rights reserved.