EXCEL FUNCTIONS AND FORMULAS

Excel formulas are equations that perform calculations on values in a worksheet. They start with an equals sign (=) and can include numbers, cell references, and operators such as +, -, *, /, etc. For example, =A1+B1 adds the values in cells A1 and B1.

Excel functions, on the other hand, are pre-built formulas that perform specific calculations. They also start with an equals sign (=) followed by the function name and arguments in parentheses. For example, =SUM(A1:A10) adds up the values in cells A1 through A10.

1. This is the fourth section of data analysis in Excel series. And we will be doing some aggregation functions. And we will begin with the “SUMIF”.

2. The first question on the “SUMIF” says, “What is the Totalunit sold in western Region”.

3. You can achieve this by using the autosum. First of all highlight the column header.

4. Go to your “Home” tab.

5. On the “Home” tab, click on “sort and filter”.

6. A menu will dropdown.

7. On the menu, click on “Filter”.

8. When you are done go to the “Region” column and click the arrow head sign beside it and a menu will dropdown.

9. On the menu dropdown click on “select All” and then click “East” out by clicking the box beside it. Leaving  only “West”   check box on the menu.

10. Then click “ok”.

11. Then scroll down, under the “Totalunit” and click on the first empty cell there.

12. Then go to your “Home” tab and click on “Autosum”.

13. Then your result will appear under the empty cell on “Totalunit”, which will be “143.51”.

14. But , actually we want to achieve or get this answer by using the “SUMIF” function. First of all click on an empty cell under the given in the orange box and type an equals to sign (=) in the cell. Then the equals to sign (=) which you typed will also appear above in the empty white space at the top.

15. Instead of continuing to type in the empty cell you click on, just continue at the top where the equal to sign (=) appeared. And then type “SUMIF(“  immediately after the equal to sign (=), which will appear like this: “=SUMIF(“.

16. After typing the “SUMIF(“ a hint will be provided for you, on how to carryout the SUMIF function. The hint will be in the order of (range, criteria, and sum of range).

17. Going by the order on how to do the “SUMIF” as I just explained above. We will first of all go with the “range”. And the range here will be taken from the “Region” column as the question implies. So we will first of all enter the range under “Region” column from top to down which will be “SUMIF(B2:” (From top).

18. And then scroll down to check the last number under the “Region” column which is “181”.
19. 1scroll back at the top and enter “SUMIF(B2:B181” (This is for the range).
20. Next , we enter our “Criteria” (Mind you, we are still following the order I talked about in step 16. And from the question our “criteria” here, is “West”. So you will now enter the criteria which will look this: “=SUMIF(B2:B181, “West” “.

21. Lastly, we will enter the “sum of range” from our order. And the “ sum of range”, here, is our “unitprice”, so our “unitprice” will range from “=SUMIF(B2:B181, “West”, G2:G181)”.

22. When you are done click enter and your result will be displayed to be “143.51”.

23. Move to the next question in the orange box which says, “What is the Totalprice generated from cookies”

24. Following the same step as the previous one , click on an empty cell under the orange box (i.e the second orange box), then type equals to sign (=) in the cell and it will appear at the top, then you continue typing at the top and no onger in the empty cell. Type “=SUMIF(“.

25. Next we move to the “Category” column and check the “range” from top to bottom and enter it above like this “=SUMIF(D2:D181)” which will be over “range” for the question.

26. In the same order we will enter the “Criteria” which is “=SUMIF(D2:D181, “cookies””. Here “cookies” is our criteria as the question implies.

27. Then lastly, the “sum of range” which is the “Totalprice” will be entered like this: “=SUMIF(D2:D181, “Cookies”, H2:h181)”.

28. When you are done click “Enter” on your keyboard and your result will be displayed as “12476.27”.

29. We move to the next one which is “SUMIFS” (Note: In “SUMIFS” the “sum of range” comes first in the order).

30. In the first question we have in the first orange box it says, “What is the Totalprice generated from the West Region where the category is snacks”.

31. First of all click on an empty cell under the first orange box which is the question we are about to attempt them move your cursor at the top empty white space, click on it and type “= SUMIF(“.

32. Once you enter this it will give you a hint on how to solve the “SUMIFS” in an order like this: (sum range, criteria range, criteria, criteria range, criteria). These order of arrangement is different from the one of “SUMIF”.

33. Next we type in the “sum range” since from the order we are starting from “sum range” and not “range”. So we will type in “=SUMIFS(H2:H181”. 

34. Next we type in the “Criteria range” from the order which is “=SUMIFS(H2:H1818,B2:B181) our “Criteria range” here is “Region” column.

35. Next we type in the “criteria range” from “Category” column which will look like this: “=SUMIFS(H2:H181, B2:B181, “West” , D2:D181).

36. Next we type in the “Criteria” from “Category” column which will be “Snacks” and it will look like this: “=SUMIFS(H2:H181, B2:B181,”West”,D2:D181, “Snacks”.

37. When you are done press “Enter” and your result will be displayed as “777.84”.

38. Move to the next question in the second orange box which says, “What is the Totalunit price sold in the Western Region where the unitprice is greater than 2”.

39. In the same way, we will still click on any empty cell under the second orange box and type “=SUMIFS(“ at the top of the empty space above.

40. Next, we type in the “sum of range” in the order. And the “sum of range” here will be “unitprice” and it will be entered like this: “=SUMIFS(G2:G181, B2:B181”.

41. Next, we type in the “Criteria range” which is from the “Region” column. And it will be entered like this: “=SUMIFS(G2:G181,B2:B181”.

42. Next, we type in the “Criteria” which is still from the  “Region” column. And the “Criteria” will be “West”. It will look like this: “=SUMIFS(G2:G181,B2:B181,”West””.

43. Next, we type in the “Criteria range” from  the “unitprice” column, which will be written like this: “=SUMIFS(G2:G181, B2:B181, “West”, G2:G181”.

44. Next, we enter the “Criteria” from “unjtprice” where it “>2”. As the question implies. So, it will look like this: “=SUMIFS( G2:G181, B2:B181, “West”, G2:G181, “>2”).

45. When you are done press “Enter” on your keyboard and the result will be displayed. It will be displayed as “51.94”.

46. Then we move to the last question which is the third one in the orange box, which says “How many unitprice were sold in the East excluding crackers?.

47. As we have been doing previously, we move our cursor to the empty space above after clicking on any empty cell under the last orange box, and type in “=SUMIFS(“.

48.  Next, we type in the “sum range” which is the “unitprice”. It will be written  in this way: “=SUMIFS(G2:G181,”.

49. Next, we type in the “Criteria range” from the “Region” column which will be like this: “=SUMIFS(G2:G181,”.

50.  Next, we type in the “Criteria” which is “East” and it will appear like this: “=SUMIFS(G2:G181, B2:B181, “East””.

51. Next, we type in the “Criteria range” from “Category” column which is “Crackers”. And it will appear like this: “=SUMIFS(G2:G181, B2:B181, “East” D2:D181”.

52. Next, we type in the “Criteria” from “Category” column which is “<> crackers”. And it will look like this: “=SUMIFS(G2:G181,B2:B181, “East”, D2:D181, “<> crackers)”.

53. When you are done press “Enter” on your keyboard and your result will be displayed to be “191.13”.

54. Next, we move to “COUNTIF”. It is also similar with the “SUMIF”. It is also takes in one criteria.

55. The first question in the orange box here says. “Find the Total Number of Times Lawrell made a sale.

56. First of all click on an empty cell under the first orange box.

57. Type in equals to sign (=) and watch where it will appear above, then move your cursor above and continue typing. Type in “=COUNTIF(“ .

58. The “COUNTIF(“ is in order of (range, criteria).

59. Next, we type in the “range” from the “sale rep” column which will look like this: “COUNTIF(B2:B181”.

60. Next, we type in the “Criteria” which is “Lawrell”. So it will look like this “COUNTIF(B2:B181, “Lawrell”).

61. Then press “Enter” on your keyboard and then your result will be displayed to be “52”.

62. Move to the next question in the orange box which says, “Which Sales Representatives made a sale more than 3 times”.

63. Then move your cursor to the empty white  space above and type in “=COUNTIF(“.

64. Next, we type in the “range” from “Sales rep” column. Which will look like this: “=COUNTIF(B2:B181,”.

65. Next, we type in the “Criteria” from the “sales rep” column. Which will look like this: “=COUNTIF(B2:B181, B2:>3).

66. When you are done press “Enter” on your keyboard and your result will be displayed.

67. Move to the next one which is the “COUNTIFS”. Here, the question in the first orange box says, “How many orders were placed from the West Region after 30^th December 2020”.

68. Just like the previous processes, click on an empty cell under the first orange box and type equals to sign  (=).

69. Look above and watch where the equals to sign (=) you just typed will appear, then move your cursor there and continue to type “=COUNTIF(“.

70. Next, we type in the “range” from the “Region” column which look like this: “=COUNTIF(C2:C181,”.

71. Next, we type in the “Criteria” from the “Region” column which will look like this: “=COUNTIFS(C2:C181, “West”.

72. Next, we type in the “Criteria range” from the “OrderDate” column, which will look like this: “=CONTIFS(C2:C181, “West”,A2:A181,”.

73. Next, we type in the “Criteria” from the “orderDate” column which will look like this: “=COUNTIFS(C2:C181, “West”, A2:A181, “>30-12-2020”).

74. When you are done press “Enter” on your keyboard and your result will be displayed to be “24”.

75. Move to the next question which is the second orange box which says, “ How many times did Lawrel sales cookies”.

76. Then, click on any empty cell un der the second question and type in an equals to sign (=).

77. Watch carefully above where the equals to sign (=) you just typed will appear, move your cursor there and continue typing like  this: “=COUNTIFS(“.

78. Next, we type in the “range” from the “Sales Rep” column which will appear like this : “=COUNTIFS(B2:B181,”.

79. Next, we type in the “Criteria” which will appear like this: “=COUNTIFS(B2:B181, “Lawrell”.”

80. Next, we type in the second “criteria range” from the “Category” column which will appear like this : “=COUNTIFS(B2:B181,”Lawrell”, E2:E181”.

81. Next, we type in the “Criteria” from the “Category” column which will appear like this: “=COUNTIFS(B2:B181, “Lawrell”, E2:E181, “Cookies”).

82. When you are done press “Enter” on your keyboard and the result will be displayed as “22”.

83. You can also try to play around with the formulas by clicking on the answer which “22” and then pressing ctrl D to Duplicate.

84. Then go above to the entered formula where you wrote “Lawrell” and change it to “Peter”.

85. When you are done press “Enter” on your keyboard and your result will be displayed to be “3”.

86. You can still click on the answer you just got which “3” and press ctrl D to duplicates.

87. Then go to your formula above and change the “Peter” to “Adam” and the “Cookies” to “crackers”.

88. When you are done press “Enter” on  your keyboard and your result will be displayed to be “1”.