Home > Excel > Using SumIfs and CountIfs in Excel

Using SumIfs and CountIfs in Excel

In the last two quarters I’ve been increasing the amount of functions I use on an on-going basis. The functions that I’ve fallen in love with recently that I’ve been using as much as if not more than Pivot Tables with Vlookups are “SumIF” and “CountIF” and their respectable “s” versions. Why do I love them? Because they can be just as effective for creating a high level report that would have previously required me to use a pivot table followed by a vlookup.

What is SumIf and CountIf


First off what are they? They are exactly what the sound like… It’s a either a count or sum of an array based on the array meeting some set of criteria. For example, I might want to know how many keywords I have in my Google Adwords account? (CountIF) or How many clicks all of my Mobile Targeted campaigns have received (SumIF.)

What is SumIFs and CountIFs


The plural form means that the sum or count only happens within the rows of the array when it meets multiple set of criteria. For example as a marketing manager in paid search I might want to know the count keywords on Google that are active (CountIFs – Counting Keywords based on two variables: SearchEngine=Google & Status=Active).
I might also want to know the total cost of all keywords on Google with Mobile Targeting (SumIFs)

How to write the SumIF OR CountIF Function

SumIFs and CountIFs are very easy formulas to write, if you have issues remembering the order of the criteria vs sum array look at the hint in the formula bar. Below is a sample set of data along with a CountIF function in action.
=CountIF(Range, Criteria) =CountIF(What do you want counted, What is the variable that should be counted)

=SumIF(Range, Critiera, [Sum Range]) =SumIf(Where to look for the variable, What is the variable, [Set of data if criteria are met that you want Summed]

Example of writing a CountIf Function in XLS

Taking it one step further as I mentioned above you when explaining the SumIFs and CountIFs variables you can create a table that looks up a preset data format and have it return variables based on multiple sets of criteria. For me this is very helpful with weekly reporting because I get my performance data in the same format each week. I Can essentially create a preformatted table where my data will be delivered and have the COUNTIFS/SUMIFS provide my high level summary of information on a “Performance Dashboard” that then would get distributed to my manager or leadership team.

Here is an example of using CountIFs and SUMIFs in an excel workbook:
XLS-CountIFs-SumIFs

I hope that you find these functions as useful as I find them and that you can save a little bit of time in your work day or work week by adding these simple formulas into your arsenal of XLS awesomeness!

Advertisements
Categories: Excel
  1. May 8, 2014 at 12:39 am

    Does your site have a contact page? I’m having a tough time locating it but, I’d
    like to send you an email. I’ve got some suggestions for your blog you might be interested in hearing.
    Either way, great blog and I look forward to seeing it expand
    over time.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: