Conditional Counts and Sums
This course covers Conditional Counts and sums and consists of 17 topics.
COUNTIF and COUNTIFS Functions –In this video we show how the COUNTIF and COUNTIFS functions are used to do conditional counts on single or multiple criteria.
COUNT, COUNTA, and COUNTBLANK Functions –In this video we show how COUNT, COUNTA, and COUNTBLANK functions are used to count cells in a range containing numbers, non-blanks, or blanks.
SUMIF, SUMIFS, AVERAGEIF, and AVERAGEIFS Functions –This video shows how the SUMIF and AVERAGEIF functions are used to calculate conditional sums and averages based on a single criterion. It also shows how the SUMIFS and AVERAGEIFS functions are used to calculate conditional sums and averaged based on multiple criteria.
SUMIFS With Duplicates –This video shows how the SUMIFS function and the Remove Duplicates functionality are used to calculate the sales each sales person made of each product.
Subtotals –This video shows how SUBTOTALS are used to determine how much each salesperson sold in each region.
Data Consolidate –This video shows how DATA CONSOLIDATE is used to combine data from several workbooks to calculate total sales of each product during each month.
Database Functions –This video shows how the DCOUNT, DSUM, and DAVERAGE functions are used to compute conditional counts, sums, and averages. It includes doing conditional calculations based on an “OR” criteria.
Computed Criteria –This video shows how Database functions are used with criteria based on an Excel formula.
DGET Function –This video shows how to use the DGET function to pull the location of a transaction based on the date and ID code.
Advanced Filter –This video shows how the Advanced Filter can be used to extract any subset of a spreadsheet’s data and copy this data to a new location.
Conditional Counts and Sums With Non-Blanks – This video shows how to do conditional sums and counts where one criteria is that the cell does not contain a blank.
Benford’s Law – In this video we show (amazingly) that for 70% of all countries the first digit in their population is 1,2,3, or 4!
Finding the n’th Match – In this video we show how to find the n’th match to a word in a column.
Analyzing NFL Play Effectiveness – In this video we show how to use text functions, IFERROR, COUNTIF, and AVERAGEIF functions to analyze the effectiveness of Adrian Peterson’s off tackle runs.
Using the Sumproduct Function for Conditional Sums and Counts – In this video we show how to use the amazing SUMPRODUCT function to calculate conditional counts and sums
Using OR Criteria with Conditional Counts, Sums, and Averages – Usually conditional counts, sums, and averages utilize AND criteria. In this video we show how to utilize OR criteria with these functions.
Using the Sumproduct Function for Conditional Sums and Counts – In this video we show how to use the amazing SUMPRODUCT function to calculate conditional counts and sums.