24. SUMIF: Startup and investor excel model training

Intro to Excel model training

This is part of a series on Excel model training focused on helping founders and investors to actually enjoy and get proficient in excel. It’s such an important skill to have, and there’s no excuse to not be able to do all the ‘advanced’ stuff, let alone the basics.

This is a pragmatic course where you will only learn the most useful things; those that I actually use regularly. We’ll go through the obvious applications, but also include some hacks I’ve developed over the years.

To get the excel example for this and all the other instalments, subscribe with the pretty box on the right and you’ll get the excel tip of the day sent directly to your inbox. Within a month and ten minutes practice, you’ll be a boss in no time.

You can join up to the training here.

What’s the point of SUMIF?

SUM adds stuff. IF let’s you ask quetions. Their baby is SUMIF.

SUMIF sums if something meeting your demands. We just did SUMPRODUCT and included a conditional. This is a faster way of doing the same thing as that, except SUMIF is less of a cognitive load for you ;). They’re easy to bust out.

A lot about making an excel model is just getting a lot of things done as fast as possible and making it easy to audit when you can.

How do I do these things you speak of?

SUMIF is realy simple. Therea re three parts to the formula.

The Syntax of SUMIF is:

=SUMIF(range, criteria, [sum_range])

range’ are cells that you want to question like the Gestapo according to your ‘criteria’.

criteria’ refers is the question you ask and they items that pass the grade are added. ‘criteria’ can be a number, expression or a text string.

sum_range’ is actually optional, it specifies the cells you want to add up. BUT, If ‘sum_range’ is not included then SUMIF treats the ‘range’ as ‘sum_range’.

So what’s the point of the sum_range then? Well you can make the range one thing to question, and the sum_range what you add up!

Example: SUMIF(list of politicians, “are corrupt”,sum their bank balances)

Get it?

Maybe you want to add up staff who are ‘managers.’ There you go.

I use it in my fundraise templates to add up the costs and headcount as you can see below. This saves me a tonne of time and makes the model flexible in case you want to recharacterise staff or costs in the assumptions.

2017-07-28_13-22-39.gif

Example

The excel example sheet will help you to learn this with an example.

sumif.png

To get the training model, subscribe to the series on the right.

Get in the game

Free tools and resources like this shipped to you as they happen.

We won't send you spam. Unsubscribe at any time. Powered by Seva

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.