The SUMIF function is a direct way to sum values based on a single criteria. If we want to sum or add values based on multiple criteria however, then we need to take extra steps. One alternative for this is to use the SUM function and the IF function.
In the example below we want to [...]
One of the options in the Data menu is the Subtotals. It allows you to create subtotals for a specific column in your worksheet, based on a key in another column. The subtotals will be added to the original data which will be retained as it is.
The best way to explain how to create a [...]
In a previous post I covered counting how often a value occurs within a range. In that post we looked into one criteria, and we used the COUNTIF function.
But what if we want to count cells that meet multiple criteria. We can not use the COUNTIF function in this case, so we will take a [...]
To count how often a value occurs within a range we use the COUNTIF function.
Because this function count cells based on a given criteria, that fact will help us limit the count to our specified value.
If in the example below we want to count how often “John” appeared in the list, then we will use [...]
What is a pivot table?
Pivot table is a feature of Excel that takes a long list of data, summarizes it into a shorter list that is more easy to analyze and assimilate.
The best way to explain how to create a pivot table is by using an example.
In the image below you see a list of [...]
If you have a column of data that contains duplicates, then you can use the advanced filter option to identify and copy the unique values to a different location. You can then easily count those values using COUNTA function or the ROWS function.
Perform the following steps to achieve this goal:
1. First make sure that your [...]
It may be interesting to know that the Find option in Excel Edit menu is not limited to finding cell values, but it can also be used to search for text in a formula or a comment.
To search for a text in a formula follow these steps.
1. In the Edit menu click Find.
2. The Find [...]
To count numbers greater than or less than a given number, you use the COUNTIF function.
As asn example if you have a list of numbers in a range of cells B1:B20 and you want to know how many of those cells have a value greater than 100, then you use COUNTIF as follows:
COUNTIF(B1:B20,”>100″).
On the other [...]
The COUNTIF function counts the number of cells within a given range that meet a given criteria.
Syntax
COUNTIF(range,criteria)
Range: The range of cells from which you want to count cells.
Criteria: The criteria upon which you want to base your count. It defines which cells will be counted.
The criteria can b a number, expression, cell reference, [...]
To count cells, within a given range or list of arguments, that contains data, you use the COUNTA function.
Data in this context includes error values, spaces and empty text (””).
Syntax
COUNTA(value1,value2,…)
Examples:
COUNTA(A1:A20) :counts any nonblank cells in a range of cells.
COUNTA(A1:A20, 7,”ABC”) :counts nonblank in a range of cells plus the second argument (7), and the third [...]
