Tech2 Touch

Category: Excel Tips

Excel tutor on formulas, tips and tricks and VBA.

  • Different ways of using Single Criterion SUM function in Excel

    We have already seen the basic syntax and how to use single criteria SUM in Excel. In this post, we will see how use conditional sum based on difference scenarios. For detailed explanation on SUMIF function please read here.
    Examples for Using conditional sum Excel Function SUMIF: Below are the some of the Excel example based on different situations.

    Conditional sum Sample data
    Sample Data

    1. Summing only Negative OR Positive values using Excel SUMIF Function:

    The below formula will add the “difference” column values whichever is less than zero.
    =SUMIF(E2:E20,”
    Similarly you can add the values with positive value.
    =SUMIF(E2:E20,”>0″)

    Now if we want to find the total variations, we need to sum all negative values and positive values. Below is the formula to find total sum considering absolute values.
    =ABS(SUMIF(E2:E20,”0″) which returns 86. Here we did calculate the sum of both negative and positive values and then added these two making negative values absolute. 

    Note: The SUMIF function can use three arguments. However it is not mandatory to use third argument. If we omit the third argument, then Excel adds the values in the 1st argument when the criteria is met.

    2. Summing values based on a different Range:  For this we need to use the third argument of SUMIF functions.  As we know, first argument is the range which should be matched with the criteria(argument 2) and the third argument is the range which should be added when given criteria is met. Following SUMIF formula demonstrates the same.
    =SUMIF(E2:E20,”>0″,C2:C20) which returns 940. In this example, excel adds the “price” where the difference has positive value(Greater than Zero).
    3. Summing Values based on a text comparison: Some times we need to compare the text value of a range and then perform SUM.  Following are examples for the same.

    =SUMIF(A2:A20,”Apple”,C2:C20) which returns 444. This formula adds the range “Price” when “Data” Column has the text “Apple”.
    =SUMIF(A2:A20,”<>Apple”,C2:C20) which returns 997.This formula adds the range “Price” when “Data” Column does not contains text “Apple”.

    4. Summing Values based on a date Comparison: We can use Excel SUMIF formula to add the values based on given date criteria. Following is the example for the same.
    =SUMIF(B2:B20,”>=”&DATE(2013,1,7),C2:C20) which returns 790.
    =SUMIF(B2:B20,”>=”&TODAY(),C2:C20) which returns 217.

    Note: As you might have observed, we have used a expression as a second argument which is a criteria. The expression used in first example is DATE and in second it is Today().DATE function returns the date and Today function returns Todays date.  Also the comparison operator, enclosed in a quotation mark is concatenated using & operator with the result of the DATE or TODAY() function.

    Please share your thoughts or queries on these examples and also share any tips for using SUMIF function you might know in the comment section.0>0>

  • Using VLOOKUP on Excel-Explained with Syntax and Examples

    VLOOKUP functions of excel is very useful when you need to return a value from a table or a range of cells by looking up another value. VLOOKUP on excel can be used for finding the value in a range which are present in the other range in the same sheet or other sheet and other excel files. Let is quickly look into the syntax of vlookup function and examples on how to use VLOOKUP on Excel.  Do note than simple VLOOKUP function works with only single criteria. If you are looking for 2 or more criteria vlookup, the please refer my previous post multi criteria vlookup.

    Syntax of Excel VLOOKUP Function:

    = VLOOKUP( value, table_array, index_number, Typeoflookup )
    where
    Value: is the value to search for in the first column of the table_array or range of cells. Value can either a text or a numerical value.
    table_array: is the range of cells where you need to lookup for the value.
    index_number: is the column number in table_array from which the matching value must be returned. A index_number argument of 1 returns the value in the first column in table_array; a index_number of 2 returns the value in the second column in table_array, and so on.
    Typeoflookup: This is not mandatory. However this will chose between exact match and relative match.  If you use TRUE or if you omit this argument, then non exact match lookup will be performed. If this is set to FALSE, then vlookup does the exact match
    Note and Troubleshooting Tips on Excel:
    • If  “Typeoflookup” is either TRUE or omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.
    • If “Typeoflookup” is FALSE, the values in the first column of table_array do not need to be sorted.
    • Excel vlookup returns #VALUE! if  “index_number” is less than 1 and returns  #REF! if the  “index_number”Greater than the number of columns in table_array. Also one more error value comes with VLOOKUP is #N/A. Excel returns #N/A when there is no exact match or the value you are matching in the range is not found.
    •  If there are two or more values in the first column of table_array that match the lookup_value, the first value found will be returned

    Example on Using VLOOKUP formula in Excel:

    Excel Examples
    Sample data

    Using vlookup with Exact match:

    Considering above sample data, if you need to lookup for the value “Banana” and fetch the Rating, use the formula given below.
    =VLOOKUP(“Banana”,A2:C6,3,FALSE) which returns C.

    Using vlookup with non Exact match:

    This is useful, when you need relative value. Say for example, your matching value based on the “% of commission” and you want to return the ratings. Here do not that, for using non exact match the first column of lookup range  must be placed in ascending sort order.
    =VLOOKUP(33%,B2:C6,2,TRUE) which returns C. Here first instance of nearby value is taken by the formula.

    Formatting the VLOOKUP on Excel using ISNA and IF functions:

    When there is no match found, excel returns #n/a.  This is will give problems when you need to use SUM function on the return value of returned value.To overcome this, we need to use ISERROR function. This very useful, if you are creating a template with VLOOKUP function.

    Examples:
    From the same above sample table, we will match Strawberry. The formula returns #n/a as the match is not found on the table.
    =IFERROR(VLOOKUP(“Strawberry”,A2:C6,3,FALSE),”Not Found”) which   returns “Not found”.
    The IFERROR functions syntax is =IFERROR(Value, value if error) where “value” is the data which needs to checked for error and “value for error” is the value to be returned incase of error.
    In above example we use VLOOKUP return data as value and “Not Found” used if the returned value is #N/A.
    IFERROR function was introduced in Excel 2007. Hence if you are using earlier version of Excel 2007, then you need to use IF function and ISERROR function of Excel. So the formula is as shown below.
    =IF(ISERROR(VLOOKUP(“Strawberry”,A1:C6,3,FALSE)),”Not Found”,VLOOKUP(“Strawberry”,A1:C6,3,FALSE))
    Note: Do not use this function unless you have earlier versions of excel as this will lead to calculation time. This is because for every time you use the function, excel needs to calculate vlookup twice. when range to lookup is more and number of instances of using this formula more, then excel will take really long time to calculate and return the right values.
    Please do share your feedback and tips which you know in the comment section below. If you need any specific support on Excel, then mention your query in comment section, I will try to answer your query.

    Video Tutorial for VLOOKUP

  • Using Excel Multiple condition COUNTIF function

    In my previous post, we have seen how to use single criteria Excel COUNTIF function. That works great  when you need to count a specific range of cells based on one condition.  Some times, there are situation we are required to count range of cells based on 2 or more conditions.  So let us have a quick look on the same.

    Excel Multi condition count function  with AND:

    The And criteria count function counts the cells if all the conditions are met.  The simple example for this is counting a range of cells which falls in between a numerical value. For example, counting cells that contains a value greater 0 and less than or equal to 20. Any cell that has positive value less than or equal to 20 will calculated in the Excel multi count with AND.For using this we need to use COUNTIF excel function or SUMPRODUCT function.

    Syntax of COUNTIFS function:

    =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

    Where
    criteria_range1:  Required. The first range in which to evaluate the associated criteria.
    criteria1 :  Required. The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. For example, criteria can be expressed as 32, “>32”, B4, “apples”, or “32”.
    criteria_range2, criteria2, …    Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

    Note: COUNTIFS works only with Excel 2007 and higher versions. For using multiple condition count in earlier version of Excel we need to sumproduct.

    Syntax for using SUMPRODUCT function as multicondition count:

    As we have seen earlier, SUMPRODUCT function can be used to count range of cells based on multiple criteria.
    Example for using Multicondition count in Excel: Below the sample data.

    Counting only the sales of Apple for the month of Jan with value less than 250:
    1. Using countifs function: =COUNTIFS(A2:A13,”Jan”,C2:C13,”Apple”, D2:D13,”2. Using the SUMPRODUCT function: =SUMPRODUCT((A2:A13=”Jan”)*(C2:C13=”Apple”)*(D2:D13
    250>250>

    Using Multiple condition count with OR:

    To count cells by using an OR criteria, you should use multiple COUNTIFS functions.  For example, counting the number of instances of  8,6 and 5 in the range B2:B13.
    =COUNTIF(B2:B13,8)+COUNTIF(B2:B13,6)+COUNTIF(B2:B13,5)

    This can be achieved through COUNTIF function array formula of Excel.  The below Array formula returns the same result as that of above. As this is a array formula you need to enter the formula using Ctrl+Shift+Enter.
    =SUM(COUNTIF(B2:B13,{8,6,5})

    Note:  You need use flower bracket for 8,6,5 in the above example. 

  • Excel COUNTIF function Syntax with Examples

    The Excel COUNT and COUNTA formula is useful for the basic counting requirements. However, sometime you need to count a number of cells in a range which meets given criteria. To do this, COUNTIF formula is useful. So COUNTIF formula counts number of cells in a range based on the given criteria.
    Syntax of  Excel COUNTIF formula:
    COUNTIF(range, criteria)
    where
    range is range of cells which you need to count
    criteria is nothing but the condition on which the range should be calculated.

    Example for Excel COUNTIF Formula:
    Let us take below sample data and use countif function with different criteria.

    Excel Examples

    =COUNTIF(C2:C7,50) :- Counts the range with Value 50
    =COUNTIF(C2:C7,E1) : Counts the cells value equal to Range E1
    =COUNTIF(C2:C7,”>”&E1) : Counts the cells greater than value specified in the range E1
    =COUNTIF(D2:D7,”=COUNTIF(C2:C7,”*”): Counts the cells which contains text
    =COUNTIF(A2:A7,”Apple”): Counts only the cells with exact word Apple; not case sensitive
    =COUNTIF(A2:A7,”*Apple*”): Counts cells containing text Apple anywhere within the text
    =COUNTIF(A2:A7,”*E”) : Counts cells which cell content has the alphabet E in the last
    =COUNTIF(B2:B6,”>”&AVERAGE(B2:B6)) : counts the cells which are greater than average of the range.

    Using COUNTIF function with named ranges:  You can use Named Ranges in Excel countif function.  For doing this first we need to define the range.  Let us look into the above example and define Range A1:A7 as ” “Data”.

    Excel Tips

    Now we can use the names instead of writing the Ranges. The example for Countif function with named ranges.
    =COUNTIF(Data,”Mango”)
    Similarly we can use defined names to any range by naming that range. It is useful when we use complicated countif functions.0>

  • Counting Non Duplicate Entries in a Range in Excel

    When we are doing analysis in excel or preparing the reports, we will face situations where we need to calculate the unique entries. There is no direct function in Excel to calculate only non duplicate entries in a Range. However this can be achieved through array functions of Excel. Below is the example through which we will learn to use Excel function to calculate non duplicate entries count.

    Count non duplicate values

    The above example has list of gadgets in Column A2 to A10. However there are duplicates in List. Our goal is calculate unique number of Gadgets. So the formula to count non duplicate entry is
    =SUM(1/COUNTIF(A2:A10,A2:A10)) which returns 5. i.e out of 9 entries only 5 items are unique.
    Note: For using array functions, press Ctrl+Shift+Enter(Not Just Enter). Excel surrounds the formula with braces to remind you that it is a array formula.
    This formula is first used by David Hager and then passed around on various web sites and newsgroups. The  above array formula works fine unless the range contains one or empty cells. To overcome this short fall we should use below formula with new IFERROR function.
    =SUM(IFERROR(1/COUNTIF(A1:A10,A1:A10),0))
    The preceding formula work with both values and text. If the range contains only numeric values or blank cells(But no text), we can use the below mentioned formula(This is not array formula) to count the number of non duplicate or unique values.
    =SUM(N(FREQUENCY(A1:A10,A1:A10)>0))