1) SUMIF
2) SUMIFS
3) COUNTIFS
4) IF
5) IFERROR
6) VLOOKUP
7) HLOOKUP
Formula #1 SUMIF
Before getting into SUMIFS. Do you know the difference between the SUMIF and SUMIFS function?
SUMIF allows only one condition to be evaluated, whereas SUMIFS allows up to 29 criteria to be evaluated.
First, let us cover the SUMIF function. It sums the data in a column or row based on a condition.
In the example below, SUMIF adds the values in Column D, if Column A equals September.
Note: Any crieteria can be added inside inverted commas i.e ""
The syntax of the SUMIF function is comprised of the 3 following arguments:
• 1) Criteria range – In this case A2:A10
• 2) Criteria itself – In this case September
• 3) Sum range – In this case column D2:D10
Formula #2 SUMIFS
What if you have multiple conditions?
How to get total sales for Car, sold in the India? This is where the SUMIFS function comes in handy.
The syntax of the SUMIFS function is
First is the sum range
• Next the criteria range of your first criteria
• Followed by the criteria itself
• Add more criteria ranges followed by the criteria as necessary
The below example shows the formula that finds the total sales in September for Bike in the India.
Note: Any crieteria can be added inside inverted commas i.e ""
You can include in any criteria argument such as >, <, =, or a combination of these in these formulas.
Formula #3 COUNTIFS
COUNTIFS function is shorter because it doesn’t need any additional sum range. It only needs criteria range. It counts the number of times the criteria are met.
Formula #4 IF
IF formula is mostly known to use formulas inside other formulas. In many situations, you may need data according to one or more criteria's based upon a situation that's when IF Formula comes handy
Basically in IF Formula we can use the below logic;
IF(Loging , what should happen if true, else what should happen if false). In false condition you can open another IF formula.
The formula above translates to:
If the value in D is more than 1500 then write “Profit” in E, otherwise leave E empty.
What if you have multiple logic you can add AND in your IF formula.
In the above situation you can use IF formula as your main formula and add more than one logic in your formula.
Formula #5 IFERROR
If you work on formulas you may often come accross errors after running the formula. That's when you need to change your formula but every error given by Excel has a hidden message in it. Following hidden message in explained below.
Solution for above error are given below using which you can skip the error message
Formula #5 VLOOKUP
VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table, VLOOKUP supports approximate and exact matching, "V" stands for "Vertical" & "H" stands for "Horizontal"
In above example "Books" for which price needs to be searched in the table.
As Books source is took from E2 cell & Source range (Array) is set as A1:B10, Price needs to be searched therefore index number is set as 2 i.e second column and finally for exact match value is kept as 0
Formula #7 HLOOKUP
HLOOKUP stands for Horizontal Lookup and can be used to retrieve information from a table by searching a row for the matching data and outputting from the corresponding column.
Below is an example of the less commonly used HLOOKUP function.
In above example "Books" for which price needs to be searched in the table.
As Books source is took from E7 cell & Source range (Array) is set as B1:J4, Price 3 needs to be searched therefore index number is set as 4 i.e fourth row and finally for exact match value is kept as 0
By that we come to an end for our first Blog.
I hope you’ve found a lot of useful information in my first blog,If you’d like to learn more please stay tuned for further updates...
Comentários