Chapter 10

WORKING WITH FORMULAS AND FUNCTIONS CHAPTER - 10: USING FORMULAS FOR COMMON MATHEMATICAL OPERATIONS Structure 1.0 Learning Objectives 1.1 Counting and Summing 1.2 Count, CountA, CountBlank. 1.3 Sum, Product, SumProduct 1.4 Summary 1.5 References 1.0 Learning Objectives After studying this unit, you will be able to:  Explain Counting and Summing in MS Excel?  Explain Count, CountA, Countblank in MS Excel  Explain Sum, Product, SumProduct in MS Excel 1.1 Count and Sum The \"Sum\" and \"Count\" functions in Excel offer two different ways of looking at the numbers in your spreadsheet. If you use both functions to address the same range of cells, each one will return a different result in most cases. In Excel, a grouping of several cells is called a \"Range.\" When you enter a formula, such as \"Sum\" or \"Count,\" you need to specify a range of cells you want the function to calculate. Another common Excel term is \"Value.\" The value is the data you enter into a cell. A value can be numbers, letters, or combinations of the two. Page 8 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS 1.2 Count, CountA & CountBlank  COUNT:  The count function is MS Excel is very important. This function allows you to count the number of cells. The function has multiple variants that allow conditional counting. The count function is the basic function used for counting cells having numerical values. It doesn’t count cells with non-numeric data.  The syntax for the Count function is: =Count(“Cell Range”)  In the above figure, the total count is 6 which means, the formula has only counted cells with numeric values. Page 9 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS  CountA  The CountA function is the basic function used for counting cells having all values. It doesn’t count blank cells data.  The syntax for CountA function is: =CountA(“Cell Range”)  In the above figure, the total count is 9 which means, the formula has counted all cells with values. Please note that this function doesn’t count blank cells. Page 10 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS  CountBlank  The CountBlank function is the basic function used for counting cells that are blank. It doesn’t count cells with values.  The syntax for CountBlank function is: =CountBlank(“Cell Range”)  In the above figure, the total count is 3 which means, the formula has only counted cells that are blank. Page 11 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS 1.3 SUM, SUMIF & SUMIFS  SUM  The sum function is MS Excel is very important. This function allows you to sum the values of cells. The function has multiple variants that allow conditional sum. The SUM function is the basic function used for adding cells having numerical values.  The syntax for SUM function is: =SUM(“Cell Range”) Page 12 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS  SUMIF  SUMIF function is a conditional sum function. It add values in a given range on the basis of given criteria.  The syntax for SUMIF function is =SUMIF(“Range”, “Criteria”, “Sum Range”). In the above figure, Range is defined from A1 to A9, whereas Criteria is set to Red and the Sum range is set to B1 to B9. So now the formula will look for the word Red in the given range and will sum the adjacent cells in Column B.  In the above figure, the total sum is 179 which means, the word Red has occurred 3 times in the given range and the sum of its adjacent cells is 179. Page 13 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS  SUMIFS  SUMIFS function is a conditional SUM function. It adds values in given ranges on the basis of given criteria. This function support multiple criteria for multiple range and the AND operation is performed between these criteria’s.  The syntax for sumifs function is =Sumifs(“SUM Range”, “Range 1”, “Criteria 1”, “Cell Range 2”, “Criteria 2”, ….). In the above figure, Range 1 is defined from A1 to A9, whereas Criteria 1 is set to “Red similarly, Range 2 is defined from B1 to B9 and Criteria 2 is set as White and finally the SUM Range is set to C1 to C9. So now the formula will look for the word Red in Range 1 along with having White in Range 2 and will perform sum function on cells where both criteria have been met.  In the above figure, the total sum is 99 which means, the word Red and word White has combined occurrence on2 points. Page 14 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS  Product  The PRODUCT function is useful when you need to multiply many cells together. For example, the formula =PRODUCT(A1:A3, C1:C3) is equivalent to =A1 * A2 * A3 * C1 * C2 * C3.  Syntax: PRODUCT(number1, [number2], ...)  SUMPRODUCT  The SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays.  The default operation is multiplication, but addition, subtraction, and division are also possible.  In this example, we'll use SUMPRODUCT to return the total sales for a given item and size:  SUMPRODUCT matches all instances of Item Y/Size M and sums them, so for this example 21 plus 41 equals 62.  Syntax: To use the default operation (multiplication): =SUMPRODUCT(array1, [array2], [array3], ...) Page 15 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS 1.4 Summary  The count function is MS Excel is very important. This function allows you to count the number of cells.  The CountA function is the basic function used for counting cells having all values. It doesn’t count blank cells data.  The CountBlank function is the basic function used for counting cells that are blank. It doesn’t count cells with values.  The sum function is MS Excel is very important. This function allows you to sum the values of cells.  SUMIF function is a conditional sum function. It add values in a given range on the basis of given criteria.  SUMIFS function is a conditional SUM function. It adds values in given ranges on the basis of given criteria. This function support multiple criteria for multiple range and the AND operation is performed between these criteria’s.  The PRODUCT function is useful when you need to multiply many cells together.  The SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays. 1.5 References https://www.techwalla.com/articles/the-difference-in-sum-vs-count-in- excel https://www.certiology.com/tutorials/excel-tutorial/count-and-sum- function-in-excel.html http://alqashi.com/book/book18.pdf https://support.microsoft.com/en-us/office/using-calculation-operators- in-excel-formulas-78be92ad-563c-4d62-b081-ae6da5c2ca69 https://edu.gcfglobal.org/en/excel2013/simple-formulas/1/ Page 16 of 63 All Rights Reserved. Vol. TLE001/03-2022