all right so in this video let's talk about differences between some if some ifs countif
and countifs functions and we'll start with some obvious differences and then we'll get into
things that are probably not that obvious so we have this little data set here
so let's go ahead and do a sum if function so that's conditional summing i'm going to
go ahead and sum up all the sales amounts for a particular region as a range here that starts with
our criteria range so the criteria range is going to be the list of regions in this column so i
basically need to select from here and all the way down but i'm just going to select the whole column
make this simple comma and then the criteria for this range would be the region i'm trying to
get the total for so if i do something like west that should be the criteria for this this
is not case sensitive so i can do lowercase uppercase and then finally we'll do the range
for some range so that will be this d column close parenthesis hit enter and then we should get the
total for west region so basically we're gonna add this this this you know anything that's west in
this column now that gives us the total for this function now similar to this we can use some ifs
to get the same thing done so to do that we'll do some ifs and the only difference here is that
the order of arguments is going to be a little different so here we start with a sum range first
so that's going to be sales amount comma then we have criteria range which is going to be region
comma and then finally i want this to be west so i'm going to go ahead and do this
hit enter we should get the same amount using some if and using some ifs now the obvious difference
between these two functions is that with some ifs you can do multiple criteria so what i could do
after i did this first criteria to be west i can go ahead and do a comma and do another optional
criteria range with a criteria so for example i could select this column b and i could say from
that column i would like to get jennifer and what should happen now it should get us the total for
west jennifer combination there is the total so we can keep adding more by doing another comma
and doing another pair of basically criteria range and criteria now if you want to learn about
more details about these functions i have separate videos covering these functions so there are a lot
of interesting things here you could do you can do wild cards here as a criteria and things like that
but i'm not going to get into it so before i move on to differences that actually make
a difference for dysfunctions for me i want to just very quickly do countif and countifs too
so the difference is going to be pretty much the same as a matter of fact if you just take some
if function and just copy and paste it and just change it to countif and just basically get rid
of the sum range argument out of here that should get us the count so now it's going to give us
basically we have five sales in west region so it's going to count one two three and four five so
that's that and similar to this if i take the sum ifs function copied hit escape go back here paste
and switch this sum if to count ifs and then i'm gonna remove the sum range out of here which is
the first one in this case so that should give us how many we have of this combination west jennifer
now let's get to less obvious differences so the biggest difference for me between these functions
is that some if unlike some ifs can actually handle a race so let me demonstrate what i
mean by that if i just go ahead and delete this and let's say we just list a few regions here
now if i wanted to get the total for each one of these regions i could do equals sum if and then
i would take the range which is going to be this criteria range and in this range i need it to be
south in this lineup list comma and i need to sum these sales amounts so if i'd enter and
as you can see we have this suggested auto fill so that just dragged this formula see basically
just google sheets helping us to drag that formula down however this is not gonna happen if we just
add another region here so we need to manually go here and drag this down to get the next one over
so let's remove these two get back to this first formula and what i'm gonna do
right in here where it says f2 i'm gonna just convert that to an array so i'm gonna remove
that one reference as a criteria and i'm just going to select something like this and then
i'm going to do ctrl shift enter to add array formula but that didn't work let's try it again
so there it is array formula around this so it works as an array formula i hit enter and as you
can see we get this automatic drop down effect so now we could go here and do something like
north east and get the total for that one see it automatically just happens because now it's an
array right now some if can handle a race countif will also handle a race so if i just copy this
hit escape and paste it over here and just do countif and again we'll remove the sum
range out of here and hit enter and you can see how it automatically just drops this down right
however if i try to do this using some ifs or countifs it will not work the same way so if i do
the sum range here comma then i'm going to do the criteria range which is this comma and then when
i get to criteria again if i just select one it's just gonna work like it works as usual however if
i try to do an array similar to this ctrl shift enter to add my array formula wrapper i hit enter
that does not work because some ifs countifs all of these don't basically handle a race
now the last thing i'm going to do here to remove these zeros
i'm going to go to this range which is our criteria range from f2 through f7
and i'm going to add a filter to this so i'm going to put that inside of a filter function
just like that and as a condition for this filter i'm going to check if that same range
is not equal to blank and now when i hit enter you'll see these zeros are gone because we
basically just filter to not use these blanks and we get our totals and now if i just go back
here and add a new region that will work and you could expand this criteria range over here
if you want to just keep adding more to this list just like that and again we could have done the
same thing with countdiff by simply just taking that same formula here converting that to count if
and then just get rid of this sum range out of here and keep everything else exactly the same
that should work the same way so the last difference i'm going to talk about
between some if and some ifs is that when you sum if the sum range is actually
optional this last argument with some ifs it's mandatory let's do a quick example here if i take
the sales amounts and i want it to sum anything above 5000 in that column i could simply just say
greater than or equal to 5000. and what this is going to do it's going to sum anything in
this column above 5000 just like that now technically you could do a comma and add
that d column all over again as the sum range but since the same column is the sum column and the
same column is the condition you don't have to do the sum range now to do the same thing with
some ifs we must select that sum range so we're going to do this comma then we're going to do
criteria range being the same and then we're gonna say it's greater than or equal to 5000.
and that should do it for this video thanks for watching
please subscribe and i'll see in the next one