COUNTIF - COUNT function counts numbers in a range, while COUNTIF function counts values that is provided in the function as a condition, e.g. If you want to view count of available numbers in a range, then use COUNT function but sometimes we would like to know how many times a value is appeared in a column. Let us say in column A, Salesmen's names are appearing, we want to see how many times single salesman name is appeared in that column. Then we will use countif function. i.e. =COUNTIF(A:A,A1) , It works as follows, in A1 salesman name is "BOB" and we are looking for how many times his name is appearing in column A (or in any other column). COUNTIF function returns number which shows how many times BOB is appearing in column A. Apply filter if you would like to see salesmen appearing more than once in a list. The same thing can be done by using pivot table.
SUMIF - SUMIF is function used for doing sum based on condition. This can be used when you would like to sum based on a condition usually for a summary report. Let us see with an example.
Suppose you would like to see total of sales of a product in a summary report. Data includes sales figure by diffrent cities, e.g.
Now to create summary report, to see the sum of total sales by city. List the city names of which summary report you would like to create. e.g.
Now insert following formula in sales column and drag it till Row 4.
=SUMIF(B:C,G2,C:C) . It will first ask range range to be used for doing sum. Here I have selected range B:C i.e. City & Sales column from first table, then condition is supplied i.e. city name in G2, and finally range is given on which sum needs to be done. so formula will look city name from G2 in range B:C and sums the numbers from Sales column from first table which matches the city name and returns the total sum viz.
Again this can be also done by using pivot table.
No comments:
Post a Comment