SUM IF FORMULA IN ADVANCED EXCEL – Today, we are going to learn SUM IF in Advance Excel features, so let’s start with basics of Formula. Lets Start with SUM Formula first . SUM formula is used to Add numerical value to a particular cell Horizontally or Vertically i.e. Row wise or column wise. But SUM formula is not that easy it is used widely by professional to increase the speed of their work by applying it differently in different modes, so we are going to study some Modes used by Professionals:
You can see how a formula works in Picture Below:
SUM Formula – So, what we are going to learn today:
1 SUM Formula
There are 4 Ways to Apply SUM Formula.
Adding value through Basic Formula of Sum, we have to prepare data like given in Picture. Prepared a data of different Brand under which LEDs & Mobiles are sold. Prepared a data on which formula will be applied in 4 different ways
Way No 1: Adding Cells one by one
It is the easiest way to add cells but its not professional way but yes if you are new learner you should know this technique, as you never know when it can help you. Also it can help you when the cells are not in Continuous series.
Way No 2: Sum Formula
Use basic SUM formula as given in Picture & Select your data horizontally or Vertically, Here I have used formula Vertically. Formula will always start from = sign, like given in Picture write
Way no 3: AUTOSUM
The third Technique, can be used when you have forgotten the shortcut key by using AUTOSUM Option given in Home tab, as Shown in Picture. First step is to Select the empty cell where you want your total should be displayed, then click on AUTOSUM options then on SUM as shown in Picture
Way No 4: SHORTCUT KEY
The sum formula can also be accessed by typing ALT + = sign in your sheet & it will automatically create a formula of sum in a continuous range. It is the easiest way to lessen your work load as it ends the use of mouse or arrow keys.
SUMIF function is among the most commonly used functions in excel.
For single criteria – we use SUMIF
For Instance, In our data, we used different Brands selling LEDs & Mobiles, earlier we Calculated Total sales of all brands, Now If we want to calculate Sales for only One brand i.e. for MI, we can use this formula very easily. First Step is to start Formula with equal to sign =SUMIF, select Range of Brand Name, Then Select the Name of the Brand you want the sales for, & then Select the Amount Range as Given Below in Picture with formula generated.
SUMIFS function is among the most commonly used functions in excel.
For multiple criteria’s – we use SUMIFS
For Instance, In SUMIFS we will Calculate Total of only MI brand & only Mobile Sales, i.e. under different brands, we need to calculate only sales of MI brand and only Sales of Mobile under MI,
First Step is after writing =SUMIFS, we have to select Preference Range i.e., what we want as result, select price range,
Second step is to select brand range, as we have to filter Brand Name
Third Step, select brand we want to configure i.e., MI
Fourth Step, then Range of Products (Second Filter)
& Fifth Step, finally select which product’s sale you want to Calculate, as shown in Picture.
I hope you have learned the way to use SUM IF Formula in Advanced Excel.
For Reference Pls click on following link
Post Credit – Miss Priyanka Dhiman