Hi welcome to this Computer Tutoring tutorial and we're going to have a look at the SUMIF
function in Excel. Now if you've been following along from the previous tutorial you'll notice
that we have created this summary page at the bottom and we've worked out the receipts the
expenses the profits and everything like that, but there is a problem, there is an issue,
because to get how much money we got from client payments and to separate
all of the various expenses, we have had to put those figures
on every month. Now let's say we don't want to do that, let's say for instance we just want to put
in one formula that's just going to cover the lot and that's what we're going to do. We are going to
use the SUMIF function across multiple sheets in Microsoft Excel. let's do the na na na na na na.
All right so ordinarily if this all this data was on a single sheet we could use the SUMIF
function to do that but as it's on multiple sheets we can't,
so how do you use this SUMIF function across multiple sheets? Well as I said before let
me show you the formula straight away and then if you want to know how it works stick around!
So I want the answer in b8, notice I've gotten rid of this top bit here,
the client payment because they're basically all receipts. So clicking b8 and I'm going to then
type in a SUMPRODUCT function and then, the SUMIF we're interested in and then an INDIRECT for the
range that we need to check. Now what I'm going to do here is to type in a double quote single
quote double quote (“’”), so double quote single quote double quotes(“’”) use an ampersand (&) to
concatenate and what we're going to concatenate is a list of all these months. So I'm going to
select all those months, I'm going to press F4 on the keyboard the function 4 key which puts the
dollar signs in to fix those references. I'm then going to put another ampersand in double quote
single quote exclamation mark, and now I need to choose the range of cells I want to use for
checking the criteria, so if I click on april and it's the category this column here is f,
so I click on f like so, now if you look at the top here you see it's got april apr rather and
the exclamation mark, I do not need that apr or the exclamation mark, so I've highlighted them
and press backspace on the keyboard to get rid of them, okay and then what I'll do is I'll close off
the quotation marks and then I will close off the brackets and now if you look at the SUMIF formula,
I type in a comma, now if you look at the SUMIF formula it's asking for a criteria, so to do that
we click on the summary sheet just down at the bottom and click on cell a8 because that's the
criteria, so basically it’s going to look at all of these cells in f to see if there's a consumable
in there and the INDIRECT function allows it to work with these months just here, incidentally
these months need to be exactly the same as this, I'll have a bit of explanation later on,
let's get on with the formula. If I just click here and then I'll type in another comma, and then
I'm going to use another INDIRECT function open double quotes single quote double quotes ampersand
to concatenate, select these ones here again F4 to fix the reference, another ampersand, double quote
single quote exclamation mark, and then if I go to april , but I can go to any one of the sheets,
and this time I'm interested in h column because that's where the expenses are,
again I don't need the april because that's taken care of with the INDIRECT function so let me get
rid of that, click at the end of the h there double quote one bracket to close the INDIRECT
one bracket to close the SUMIF and a final bracket to close the SUMPRODUCT press enter.
And there we go. So now that SUMIF will work, so if I go up to the top
and I double click just to autofill down, it will automatically pick up
all of the different categories and how much money I spent on each category. So there you go,
SUMIF across multiple worksheets in Microsoft Excel. We're going to do one more use of this
as well. We see we've got april through to march here and I want to know how much profit I made,
so if I was doing this with the standard SUM function I'd write something along these lines,
equals SUM, you don't have to do this just watch this bit, so equals SUM open bracket
and then I would basically click on april like so and then highlight
h across to I because of this being an accounts report all the expenses are in negative numbers
and then the received is in positive numbers, so when I press enter I get my profit amount for
April. Now I want to do the same for may june july august and I don't want to have to do that same
formula it takes ages. What I want to do is click on that, double click go down and see if it works,
and as we can see it doesn't work, you can notice that it has the same amount, and the reason is,
if I double click in what should be may it's still looking at April. So what I would need
to do is double click on April, type in may press enter and then go down and do the same for June,
go into June, double click on april type in jun to pick up the june sheet
and july and august I'd have to do the same it's taking forever, so obviously it's not the greatest
way of doing that, let me just remove that, and use the INDIRECT function for that. So how do we
use that? So first we start off as normal, equals SUM open bracket but then we can write INDIRECT
open bracket and then again because we are going to use this d5 to represent these sheets here,
and just in case your sheet names have spaces, in those spaces, in Excel if you're using sheet
names and you're referring to those sheets in Excel you would need to include single quotes so
that's why I'm doing double quote single quote double quote ampersand, okay choose d5, don't
put any dollar signs in because we're going to relatively reference that down, and double quotes
open single quotes exclamation mark and then I can just go to april and select h to I, and again
I don't need the april because that's taken care of in the INDIRECT, there you go click on the end,
close off my double quotes close off my brackets for INDIRECT close off my bracket for SUM
and press enter, and then we can see this is the total amount of money I've made for April. if I go
back up to the top and then double click it then picks up the name for may june july august etc so
I know how much I've made each month, and last but not least I'm just going to do a little tarting
up of this, getting alternate rows using the format painter to do that and there you have it.
All right then so I think we all deserve a little explanation of how exactly this SUMPRODUCT SUMIF
INDIRECT function works, and to help us do that we're going to have a little experiment. It's not
exactly the way I would do it but it'll help us understand how it's working. Now what do I mean?
Well if you look on our current Excel spreadsheet the way we've worked the profit out here, we've
got the profit for april and may etc and I've got the consumables, now let's say for instance I want
the amount for consumables that I've spent in april and everything broken down monthly,
well using the SUMIF INDIRECT statement without the SUMPRODUCT
can help us understand how it all works, so let's do that. So instead of profit if I click to the
right here and I'm going to type in consumables, if I can spell it correctly there we go, I'll tell
you what I'll just do a quick format painter as well to format that and bring that across,
all right then so what I'm going to do this time is I'm going to type in my formula so,
equals SUMIF open bracket, so I'm not including the SUMPRODUCT to sum or bring all the numbers
together, I'm then going to use the INDIRECT function with a, bracket, open double quotes
single quote double quote ampersand, and then select all of these months here,
I'm going to do all of these at once, okay press F4 space then an ampersand,
then another space double quotes single quotation mark exclamation mark, so now I need to check in
April, I can go to any month it doesn't really matter and it's the f column that I'm looking at,
and again at the top here I don't need that exclamation mark with the April, so let me get rid
of that, click at the end double quotation marks, close off my bracket for INDIRECT and press comma,
that's great so now it's looking for the criteria as you can see, so if I go back to summary and
this time it's consumables, I click consumables at the top now because this is going to be moving
down I'm going to just press F4 on the keyboard to fix that reference yeah which is pretty good
and now I am going to type in a comma and now type in another INDIRECT so let's just do that, again
open double quotes single quote double quote, and then I select all the months here like so,
have a F4 in there space and double quote single quote exclamation mark, go to any
month and this time I'm looking for column h get rid of the exclamation mark and april ,
close off my quotation marks, close off my bracket for INDIRECT, and close off my bracket for SUMIF,
now if I press enter, can you see what's happened here? What it's done is, it's taken, let me just
go back to the function in fact if I double click so, what it's done there is, it's using the SUMIF
it's using the INDIRECT but I am not using the SUMPRODUCT. what the SUMPRODUCT function
in effect does is sum them all together, and then puts it into one nice figure, which at the moment
we have over here in consumables. So if I just press enter, if I just highlight all of this,
and if you look down at the bottom just here you can see that the sum of all of those added
together is 4866 pounds and 23 pence. So there we go, so that's how it does it. If I wanted to
do the same for sustenance, I can type sustenance at the top here, so sustenance, and you know what,
if I'm really clever with this one, I should be able to highlight all of this, copy them, click
at the end here and then under paste special, there's a nice option for transpose, it puts them
to the side, and then if I click on consumables because I like the look of that, format painter,
and highlight all of these, and then instead of me typing in the formula again, I'm just
going to adjust this formula a little bit, so I need this f here to be fixed, so I'm just going
to highlight these two f's and type dollar signs in front of these ones, I think that'll be good,
just in case they don't move across when I'm going across, there that's good,
Summary F4 INDIRECT that's good, so this top one is I'm just going to take off the dollar in front
of the f, yep so it's going to keep row 4, but it's going to move across to this next one here,
if you don't understand about the dollars and absolute references please have a check out, I'll
give you a link to an absolute reference versus relative reference video, and over here I'll do
the same, add another dollar in front of this h and a dollar in front of this h, if I press enter
I still get the same amounts, but what I should be able to do now, is auto fill this across, so I can
just see the different amounts here. So I should be able to do the whole lot, all the way across,
so these ones here will all work, okay I'm just going to format these all as the same types of
numbers, highlight them all ctrl shift and number four, and there we have it. So you can now see
how much I've spent across different months on different categories, through a matrix, and just
to highlight that, you see sustenance on the left is 4071 pounds 76 pence, so if I highlight all of
these down and if I look down at the bottom on the status bar you can see 4071 pounds and 63 pence.
Now just to be absolutely clear, if I encompassed and surrounded this function with the SUMPRODUCT,
or in fact I should say these two functions SUMIF and INDIRECT with the SUMPRODUCT function
it will sum all of those together to get that amount. So that's what that SUMPRODUCT does.
I don't need it for here and I think it's quite a nice little function, I think that's quite nice.
That's great, excellent! Let me just get rid of this last bracket as well. Brilliant, I think you
will agree that using this SUMIF across multiple worksheets in Microsoft Excel function is a pretty
handy little arrow to keep in your quiver as it were! So make sure you bookmark this, make sure
you subscribe and click on the notifications button so you don't miss the next tutorial,
and what are we going to do in that? Well at this moment in time all of our spreadsheet,
all of our data, is in one file in one what they call an Excel workbook, now what if your files
are all over the place what happens if they're in different files in the same folder and you still
want to bring them all together into one? That's what we're going to look at next time so make
sure that you subscribe. If you've got anything out of this please give us a thumbs up it really
appreciate that so just click on that like button if you're on Instagram or Facebook or twitter then
reach out to us there be great to hear from you! I've also just started a new tick tock channel
we'll do short sharp sweet Excel and other tips as well on there we try and keep them
to two minutes or less, so please make sure you subscribe to that if you've not already done so
I think that's about it so I just want to wish you all the best for your Excel future.