How to Use SUMIF and SUMIFS Formula In EXCEL

so let's assume you have a long list of items similar to the list of inventory here in my example i have 50 rows and six columns with information on the item's name the color size unit price quantity in stock as well as quantity in reorder now the first question might be what the quantity of total items in green is that are still in stock to calculate that number you can simply use the sum if formula typing that in you can see the arguments that the formula requires first you should define the range then the criteria and finally the sum range by range the initial range is meant in which you're looking to find your required items in our case you're looking for green items so the column in which the item's colors are specified is our range just click in the first cell and pull down the entire column however now imagine you have a list of thousand rows pulling down a formula might be very tedious so instead of pulling the range down you can just click in the first cell and then press ctrl shift and the arrow downwards to get to the last filled cell of that range the second argument is the criteria which is obviously green because we only want to know the total quantity of green items just type in the word green with quotation marks finally you can define the sum range which is the quantity in stock in our case again just click in the first or here in our case the last cell and then click ctrl shift arrow up and you have marked the entire column so if you press enter your sumif formula will provide you with the number of green items that are still in stock which are 178. let's maybe quickly check that result and insert filters to our columns by clicking ctrl shift t having marked all of the headers and filter for all green items summing up the quantity in stock we see that this also returns 178 so our formula is correct alternatively to just having our conditions namely the green items as a fixed non-dynamic one can also have it as a dynamic condition just type in the condition you're looking for in a separate cell and link the cell with your formula back to that condition instead of having typed in the word green statically into your formula if you're linked you do not need the quotation marks anymore as you can see the formula also works perfectly fine being dynamic now let's assume that there are two conditions that you would like to meet so for example you're looking for the total number of medium-sized red items that are reordered now here you have two instead of one criteria but the formula is quite similar just type in sum if s where the s represents the fact that there are several conditions that need to be met now here with some if s you first need to enter the sum range which is in our case the quantity in reorder click in the first cell and with ctrl shift arrow down you can mark the entire column the next argument is the first criteria range which is in our case the red items so mark the column in which the color red is indicated the third argument is the first criteria namely red so again just type in red with quotation marks directly into the formula having typed in the first criteria we come to the second and again you first need to define the criteria range followed by the criteria itself since we want to filter for all medium-sized red items we have the column with the size as our second criteria range mark the entire column as the criteria range and enter medium as the criteria finally after having entered these two conditions press enter and the formula will return the quantity of medium-sized red items that are currently being reordered similar to our first example let's check if the formula is correct and filter for red and medium sized items summing the quantity of items for reorder we can see that the formula also provided us with the right amount of 192. also let's change the current static formula to a dynamic one by linking the conditions our first one was red and the second one medium note the words down into separate cells and link the formula to these two cells respectively now having a dynamic formula it also becomes much easier to change the conditions for example from red to green looking for medium-sized green items we're getting 472 which is confirmed by our manual check further we can change the second condition and look for small instead of medium-sized items and again the manual check confirms the results of our formula if you want to copy and paste the formula with the current ranges from cell j10 to any other cell make sure to

fix the cells by clicking f4 for each argument having fixed rows and columns you can now just copy and paste the formula wherever you want with the ranges staying exactly the same if you would be copying and pasting a formula whose cells are not fixed as in our first example the formula would return an error or a wrong number because the ranges have changed and moved in the same manner as the cell in which the formula is entered i hope that was helpful to you that's it for now and make sure to like subscribe and see you next time