Right now we're getting into the really good stuff countif, sumif, averageif this is where we're starting
to build formulas that really open the door to let us create some really cool powerful dynamic things
in Excel.
Or in our We're looking at count if some if and average if and I'm going to skip this slide and I'll
tell you why.
Because each of those functions also have a pluralized aversion.
So count if's some ifs and average ifs with an S at the end.
The only difference is that these functions allow you to do the exact same thing but include multiple
criteria instead of just one.
And the syntax is kind of different so rather than teaching you the syntax for the singular versions
and then immediately teaching you different syntax for the pluralized versions.
Let's just focus on counting ifs some ifs and average averages because you can use these versions of
the formulas even if you're just using one criteria but they open the door for you to include additional
ones if you choose.
So these are kind of the ideal ones to use.
I actually haven't used a singular version in God I don't even know how long.
So let's focus on these for now.
And basically what these do is they allow you to evaluate a count some are average based on multiple
conditions or criteria.
So syntax wise count if basically consists of a combination of criteria ranges and criteria.
So you'll see criteria range one criteria one criteria range two criteria two.
And this extends for as many criteria as I want to include.
So let's say our criteria that we want to factor into this count is the campaign name.
So we want to take the count where the campaign name equals Google for instance.
In that case the criteria range is going to be whichever column contains my campaign names and the criteria
one criteria itself is going to be Google in quotes as a string.
Some ifs and averages work the same way there's just one additional piece and that's right up front
.
You lead with either your some range or your average range.
So the sum range is the range of actual values that you want to take some of followed by all of your
criteria ranges and individual criteria.
So I know it's a little bit confusing at first but I promise once you read a few of these they'll start
to become really really natural.
So let's just kind of slowly take some time to walk through these three examples listed here.
First off count diffs.
So again we're just listing the criteria ranges followed by the criteria.
So my first criteria range is Column B.
I'm just looking at 13 rows of data so beat to be 13 which is different tactics either search or display
.
In this case only take the count would be equal search and D just like Clix column is greater than 200
and you're probably wondering hey that's a numerical filter why you put in quotes around it's not a
string.
And I wish I could tell you why that's the case.
It's just the case when you use greater than or less than in some if are counted for an average if for
some crazy reason you've got to surround it in quotes.
Trust me I I tried 1000 other ways kept getting errors.
Didn't know why and then realized this is how it works.
Again another one of those little nuances that you learn once and then you never forget it.
So that's count if it returns a result of three because there are three instances where be equal search
and click lying is greater than 200.
So some ifs.
Now I need my sum range first remember.
So I'm indicating or at least some F's column D.
This is what I want to some where column A equals February and column B equals display.
So it's going to look at all the rows where both those cases are true and then take the sum of the Clix
column which is my sum range.
Same exact thing with averageifs.
So the average range is going to be in D-Mont. clicks again.
And I want to average column D when Column A equals January and Column C equals Amazon.
So there you go that's countifs sumifs and averageifs in the next lecture we're going to talk about
putting these into practice to build our first dynamic dashboard so get pumped