Learn Microsoft Excel Online FreeSometimes in statistic, data is grouped by every N rows, for example if data is grouped by week, then data is grouped every 7 rows (or columns in some cases). In our daily life, we may meet many cases like this. It is necessary for us to have the knowledge of sum data by every… read more »
No matter you are familiar or not familiar with excel functions, I think you must know SUM function, one of the most common used functions in excel. Generally, we often use SUM function to sum data in a fixed range in daily life. But if we want to sum entire column or row without boundary,… read more »
This post will guide you how to count the number of cells that contain errors within a range of cells using a formula in Excel 2013/2016.You can easily to count cells equal to a specific value or text string through COUNTIF function. But if there is an easy way to count cells contain errors in… read more »
Sometimes we may have the requirement that subtotal data by group in a table. To just subtotal data in a column, we can use SUM function directly; for data in a column, amount data in another column, we can use SUMIF function to subtotal data. But to subtotal data in group, we need to classify… read more »
This post will guide you how to count the number of cells that are not equal to many things in a given range cells using a formula in Excel 2013/2016. You can easily to count cells not equal to a specific value through COUNTIF function. But if there is an easy way to count cells… read more »
This post will guide you how to count the number of cells equal to criteria X or Y in a given range cells using a formula in Excel 2013/2016.You can easily to count cells equal to a specific value through COUNTIF function. But if there is an easy way to count cells equal to either… read more »
Suppose we have two lists in excel, one list records the product serial numbers, these serial numbers can be duplicate, and the other list records the sales volume for products accordingly, for more details you can see the screenshot in precondition below. To calculate the total sales volume, normally we can use SUM function in… read more »
When counting the number of times for objects appear in a list or a range, we usually record the duplicate value only once and ignore the redundant ones. We cannot apply formula with only one function to implement this in excel, so we need to update formula and use a formula combine more than one… read more »
This post will guide you how to sum entire column except header or first row value in your worksheet in Excel. How do I sum entire column in excel except for certain rows with a formula in Excel. Assuming that you want the sum of an entire column but the first row or the header… read more »
Calculating the average for a batch of data is frequently used in our daily life. But for some cases like statistic the average score in a competition, or price analysis, we often calculate the average excluding the smallest and highest numbers in the range of data. As we know we can use some formula like… read more »
We enter a list of numbers or products and there are some duplicates in the list, if we want to just do count for the unique values and exclude the duplicates, how can we do? Now you can follow the below steps to solve this question by formula quickly. Prepare a list of products and… read more »
This post will guide you how to extract number from a given test string in Excel. How do I extract all numbers from string using a formula in Excel. How to get all number from a given test string using user defined function in Excel. Extract Number from String with Formula Extract Number from String… read more »
This post will guide you how to count unique values or distinct values with a formula in Excel. How do I Count unique values or numbers in a given range in Excel. Count Unique Values Using Advanced Filter Count Unique Values Using Formula Assuming that you have a list of data that you want to… read more »
This post will guide you how to lookup a value and then return the sum of two or more related columns in Excel 2013/2016. How do I Use VLOOKUP function and SUM function to lookup a specific value and return the sum of all matches in columns in Excel. Vlookup to Return the Sum of… read more »
This post will guide you how to sum a given range of cells that if the adjacent cell meet one criteria in Excel. How do I sum All Cells if its adjacent cell is blank or equals to zero or contain a specific text string value with a formula in Excel. SUM Cells If the… read more »
This post will guide you how to sum range of cells ignoring Errors in Excel. How do I sum and ignore errors with a formula in Excel. How to sum range ignoring all error values with VBA macro in Excel. Sum Range Ignoring Errors with Formula Sum Range Ignoring Errors with VBA Sum Range Ignoring… read more »
This post will guide you how to SUMIF with multiple criteria in same column in Excel. How do I use SUMIF with multiple criteria in one column in Excel. SUM Cells with Multiple Criteria in Same Column Assuming that you have a list of data in range A1:B6, in which contain sales data. And you… read more »
This post will guide you how to sum same cell in multiple worksheets with a formula in Excel. How do I sum same cell across multiple worksheets in Excel 2013/2016. Sum Same Cell in Multiple Worksheets Assuming that you have three worksheets (1-Q, 2-Q, 3-Q) in your workbook, and you need to calculate the total… read more »
This post will guide you how to average a range of cells and ignoring all zero values in Excel. How do I Average numbers in a given range and ignore zero values with a formula in Excel. How to ignore zero when averaging a range of data in Excel. Average a Range and Ignore zero… read more »
This post will guide you how to count the number of weekends between two dates in Excel. How do I get the number of weekends between 2 dates with a formula in Excel. How to count weekend days between dates in Excel. Count the Number of Weekends between Two dates If you want to calculate… read more »
