Perfect in Excel SUMIF Function

SUMIF Function

Today I am going to tell you about Microsoft Excel's function SUMIF  you can use this function whenever you have to sum any range in excel on basis of any given condition or certain criteria. Like if you have to sum of a range where many different values. But you want sum of only those cells where your condition met than you can use this formula.

For example this is a budget sheet and you want some of those months in which you have spent more than 10 thousand Rupees. Or any conditions.

In this formula we have to give three parameters.

=SUMIF (range,criteria, [sum_range])

How to enter formula.
Start with equals to sign. Then start typing sumif from your keyboard. Then open bracket. Or if you're using Microsoft Excel 2007 or later versions you can press tab when your formula gets highlighted in formula list. It will save your time. And also you can get rid of making typing errors. Then go one by one as per below instructions.

Range is the area which you have to select for which you want to sum it should contain sum values plus criteria for sum.

Criteria you can enter criteria direct by typing or you can give a cell reference.

Sum_range is the range of those cells or area for which you want to sum. It should not contain any additional cells which you don't want to get calculate in your formula result. if you don't define this parameter. Formula will use range parameter.

This formula is very useful function. I use it in my daily life. As excel is my mostly used software.

You can write in comments how you like this blog. You can also post your questions if you require any kind of help required in any excel formula or anything related to excel. Like pivot tables, chart, creating reports etc.

Perfect in Excel If formula

If formula in excel

Lets, start with  story before knowing how to use if formula. Once a team member came to me asking for help into excel. He explained me that he got a task from his boss Which he needs to complete by end of the day. he told me he got an excel sheet which got around 8000 records. And he has to identify some records based on some conditions. I told him easy you can use some conditional formulas basis on your requirement. But he told me has already tried but to no use. He doesn't know how to use conditional formulas in excel. So I went ahead and helped him in identifing those recorda mostly I used If formula. He told me his requirement basis on which I used IF formula in excel, and Identified those records whithin minutes. So now if one of you don't face this type of situation I have explained IF formula below. 

In Microsoft Excel, IF Statements (function) can be very useful for evaluating conditions (both Mathematical as well as Logical) and giving an output based on it.

It checks whether a condition is met, returns one value if True and another value if False”.

 IF function uses the following arguments.

Formula that uses the IF function

  logical_test: The condition that you want to check.

Logical test can include 
For example:EQUALS TO you can check wheter A1=C1, you can change cells as per your requirement.

 GREATER THAN If you want to compare 2 values you can use this condition
A1>B3 or B5>100 so on..
LESS THAN 
Less than can check values between any 2  A5<100 or H1<10 so on 

  value_if_true: The value to return if the condition is True.

This is asks what value you want to see if your logical condition gets True say you put condtion A1>100
and conditions is found true then formula should show YES, then you would enter formula like this. If A1 cell value will be lgrater than 100 YES result will be displayed in formula cell. Where we have entered the formula.

=if(A1>100,"YES","NO")

Note All text values needs to be inside double quotation.

  value_if_false: The value to return if the condition is False.
Noticed above in formula I entered NO inside quotation marks that will reflect in formula cell if my logical condition will gets fall means if A1 cell value will be less than 100. Than formula will show a NO. Remember quotation marks given in formula will not reflect in formula results.

Multiple If Statement can be used to check many conditions.

Links useful to learn Excel if formula

Microsoft Support

Perfect in Excel Vlookup Formula

Hi

I am writing this blog to help you in microsoft excel. This is my first blog. So today  I tell you about

Vlookup function or formula

What is Vlookup?

V stands for vertical. This formula or function used in to lookup for values. Now you may ask why do I need a function for this purpose. Now I tell you what is the use of this function. You can see, read and understand a small table data. But this function normally used when you have a huge data and you have small amount of time.

Below is  full formula :-


VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Now understand the formula,

What is lookup_value:- The value to search in the first column of the table array. Lookup_value can be a value or a reference.

What is a table_array:- Two or more columns of data. Use a reference to a range or a range name.

What is a col_index_num:- The column number in table_array from which the macthing value must be returned.

What is range_lookup:- logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match: If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

As you can see in this image vlookup formula is applied in cell F1. 1. Here lookup value is order ID 10251 (you can select a cell as well, instead of putting any value where you can change value whenever you required) 2. Table array (range used for lookup value) is A1 to B6. 3. Column Index number is 2 this means it will give the value from column 2 or B in this case. 4. Range Lookup is set to false/0. You should also set it to false. Instead you want approximate result. False is for exact match.

If you are getting #NA error try below troubleshooting.

VLOOKUP isn’t  complicated,  but  it’s  easy  to  accidently  type  the  wrong thing or make  other mistakes  that  lead  to  errors  in  your formula. 

Now I tell you about correcting any mistakes in the  VLOOKUP syntax. Below are the steps which  describe  techniques  for  troubleshooting/correcting  #N/A  errors,  which  most  frequently  occur when  you’re  trying to f ind  an  exact  match. 

lookup_value

Does this value exist  in  the left most column  of  your  lookup table/Data Range?

 If not, and  it’s  not practical to move  the  column, you  must use  another  solution, such  as INDEX and  MATCH.

 Note  that the column's  physical  position  in  the worksheet doesn’t matter. If your lookup  table  starts  at column  R and  ends  at column  T, column  R is  the  leftmost column.

Does the format of  the lookup value match  the format of  the matching  value in  the lookup table?

 Errors  often  occur  when one  of these  values  is  not   formatted  correctly.

If your data contains spaces use trim function than use value paste and use this range as lookup value.

 If you’re using  text,  did  you  remember to  put quotation  marks around  that  text?

  If you’re  typing text directly (vs. using  a cell  reference  such  as  A2),  you  must  use quotation  marks. 

table_array

  Are you using  a relative reference (e.g.,  A2:G145)  when an absolute reference (e.g., $A$2:$G$145)  is necessary? This  is  important when  you’re copying  the  VLOOKUP formula to other  cells. 

In  this  case, you typically want to  “lock”  the lookup  table  to prevent misleading  results. To  quickly switch  between  reference types, select the  range  you entered  for  this  argument, and then  press  F4. Or, better  yet, use  a defined  name  instead  of a range; names  use  absolute cell references by default. Is your  lookup  table on  a different  sheet or  workbook?

 If so, is  it referenced  correctly in  this  argument?

 Doublecheck the  sheet names, especially if you’re  switching  back and  forth  between  sheets  as you’re  building  the  formula. 

col_index_num

Are you pointing  to  the   correct column  in  the lookup table?

  To  figure  out what this number  should  be, count over to the  right from the  first column in  your  lookup  table. Count that first column as 1.  Be  careful  here. You  won’t necessarily see  an  error  if you’re  off by a column, but Excel  may return  the  wrong data—e.g.,  March  sales  numbers  instead  of April  numbers.

  Do you see the #REF!  error?

  If so, make  sure  the  number  you specify for  this  argument isn’t greater  than  the  number  of columns in your lookup table. 

range_lookup

Have you swapped the   arguments? 

 Remember, use FALSE  for  an  exact match—e.g., when you’re  looking  up  a proper name or  specific  product code. Use  TRUE to  find  the  closest match  to the  lookup  value  if an exact match  doesn’t exist—e.g., when you’re  mapping  test  scores to a table  of letter  grades  or  income to a table  of tax  rates. 

If you’re using  TRUE, are the values in  the first column  of  your lookup  table sorted  in  ascending (A  to  Z)  order?

 If not, you  may see  unexpected  results. (This sorting  isn’t required  for  FALSE.) 

If you’re using  FALSE,  do  you  see a #N/A  error?

If so, Excel  can’t f ind  a match. This  may be  because  one  doesn’t exist. Or, a match  may exist, but Excel doesn’t interpret it as  such   because  of formatting  problems or  other  issues.

Comment if need help in any other Excel formula..