so in this video i'm going to create a formula that will accomplish something like
some if or countif with or criteria in both excel and google sheets so
i'm going to start in one of those it doesn't really matter let's start with excel
so we have let's say some sales reps here in this column let's just highlight that
and we have some sales amounts and generally if you do some ifs you can only do end condition so
let's say you wanted to do or to be able to grab let's say jennifer jose jake something like that
and get the total so this is what i'm gonna do for that i'm gonna start by typing a couple of
names here i'll do isabella and we'll do olivia and notice i'm entering those names
in columns so now i'm going to create a formula here and the way i'm going to make that formula
i'm going to take this column of sales reps right here in this column and i'm gonna do a test a
logical test to see if that equals to this and i'm gonna select these two values in columns i'm gonna
hit enter so you can see what i get out of this so if you look here i got two columns of results
the first column would now belong to isabella so if we look at isabella see like this line we get
a true then if i keep going through this list i'm going to get another isabella and that's true too
and all the other cases are false in this column now if i check this second column here
that's for olivia and as you can see here we have olivia that's true
we have another olivier here that's true and the rest of the cases are false
with the same logic if i go and add jake here and simply just change this array to also include
jake like this that should now add another column for jake and every time it's jake it's gonna say
true hopefully that makes sense i'm gonna undo to just get two columns for now so now at this point
what i'm gonna do i'm gonna take this true and false values and i'm gonna convert them to ones
and zeros instead and a very easy way to get that done is to basically use this function n
so that's function n just like that i'm gonna wrap that whole thing hit enter
and you'll see now all trues became ones and all falses are zeros so why is this useful so
if we're trying to figure out how many instances of isabella and olivia we have in this column
we could simply just count once so we can just say one two three four and there it is we have four of
them so to do that easily we could just put this whole thing inside of a function sum just like
that sum will sum up all zeros to zeros and ones to that total number so there it is four instances
of those so we got isabella olivia and then isabella and another olivia someplace
there it is four of them so that's our countif basically with or statement and you could do more
here so if i go and do jake and simply just expand this range to include that
all the three names this one is hiding behind the formula but it's there
so now we got seven of those again i'm gonna undo to just get back to two but it works just fine
now the next thing you might want to do you might not want to actually
have these values here and you might want to have them in the formula directly so what i
can do i can just highlight this part press f9 and that will basically just
hard code the array in the formula see with those names and that will be our or condition countif
and obviously you can go here add a comma and put your names comma separated in quotes
just like that so now let's try to do sum if with or so for that i'm going to undo a little bit
to get to this point so if you remember this was where i did that logical statement
and then i just converted to zero and one so what i could do now i could take these zeros and ones
and simply just multiply that whole array by this column of sales amounts
so if i go ahead and hit enter right now you'll see that what will happen every time we have
isabella her sales amount will show up here in this column and there's another one isabella
right here that says the month showed up here for olivier it's gonna be the same so we got olivia
here so you can see that amount for olivia shows up here in this column and this shows up in this
column so again if we wanted to get the total of all of these zeros will add up to zeros anyways so
the rest of the numbers will get us the total so now i can just do some of this whole thing
and that would give me the total for olivia and isabella combined now again you can hard code
those names here so i can just highlight this and do f9 and do a hard coded array you could
also type but this is easier isabella olivia and that's going to give us the same total now
you wouldn't have to have those names listed here and that gives you your total and you
can obviously add commas and do other names and finally if you wanted those names to be
maybe listed here in a row instead of different columns i'm going to grab these three names
paste it here so all i would have to do because these need to be in columns for
our formula to work over here instead of just referring to that i'll do transpose
and select those three values close parentheses transpose will basically take these things that
are in rows and put them in columns and that should still work for our formula just fine
so if i change this one to john let's actually do that correctly
there it is we'll get our totals i'm going to undo a couple of steps here again get back to this one
so all these formulas should work in google sheets you would need to convert it to an
array formula for this to work so if i just copy this formula hit escape
go back to my google sheet and simply just paste that same exact formula right here all i have to
do to make this work just wrap it inside of array formula and i can do that by either
doing ctrl shift enter or you could just write this array formula function around this and if
i hit enter i'm going to get my total see 15 041 and that should be the same thing here 15 041
and just to verify that this is actually accurate let's just do a quick check so this was for
isabella and olivia so we'll just do this plus this plus
this plus this i think that's it so if i hit enter that's our number and that
should do it for this video thanks for watching please subscribe and i'll see in the next one