Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How To Insert and Customize Sparklines in Excel
  • How to do a t-Test in Excel?
  • How To Compare Two Lists in Excel
  • How to combine 2 or more chart types in a single chart in Excel
  • Data Series in Excel

References

  • How to use Excel ROW Function
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel VLOOKUP Function
  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • How to get first column number in range in Excel

Data Validations

  • Excel Data validation allow weekday only
  • Excel Data validation must begin with
  • Excel Data validation require unique number
  • Excel Data validation with conditional list
  • Excel Data validation specific characters only

Tag: SUMPRODUCT function

Calculate Weighted Average in Excel

by

To calculate a weighted average in Excel, simply use the SUMPRODUCT and the SUM function. 1. The AVERAGE function below calculates the normal average of three scores. Suppose your teacher says, “The test counts twice as much as the quiz and the final exam counts three times as much as the quiz”. 2. Below you can find the …

Continue Reading

Count cells not equal to many things in Excel

by

This tutorial shows how to Count cells not equal to many things in Excel using the example below; Formula =SUMPRODUCT(–(ISNA(MATCH(data,exclude,0)))) Explanation To count cells not equal to any of many things, you can use a formula based on the MATCH, ISNA, and SUMPRODUCT functions. In the example shown, the formula in cell F5 is: =SUMPRODUCT(–(ISNA(MATCH(data,exclude,0)))) …

Continue Reading

Count multiple criteria with NOT logic in Excel

by

This tutorial shows how to Count multiple criteria with NOT logic in Excel using the example below; Formula =SUMPRODUCT((range1=criteria1)*ISNA(MATCH(range2,criteria2,0))) Explanation To count with multiple criteria, including logic for NOT one of several things, you can use the SUMPRODUCT function together with the MATCH and ISNA functions. In the example shown, the formula in G8 is: …

Continue Reading

Sum by weekday in Excel

by

This tutorial shows how to Sum by weekday in Excel using the example below; Formula =SUMPRODUCT((WEEKDAY(dates)=day_num)*values) Explanation To sum data by weekday (i.e. sum by Mondays, Tuesdays, Wednesdays, etc.), you can use the SUMPRODUCT function together with the WEEKDAY function. In the example shown, the formula in H4 is: =SUMPRODUCT((WEEKDAY(dates,2)=G4)*amts) How this formula works You might …

Continue Reading

Count cells over 100 characters in Excel

by

This tutorial shows how to Count cells over 100 characters in Excel using the example below; Formula =SUMPRODUCT(N(LEN(range)>100)) Explanation To count cells that contain more than a certain number of characters you can use a formula based on the SUMPRODUCT, LEN, and N functions. In the example shown, the formula in C2 is: =SUMPRODUCT(N(LEN(B5:B11)>100)) How this …

Continue Reading

Count numbers that begin with in Excel

by

This tutorial shows how to Count numbers that begin with in Excel using the example below; Formula =SUMPRODUCT(–(LEFT(range,chars)=”xx”)) Explanation To count numbers in a range that begin with specific numbers, you can use a formula based on the SUMPRODUCT function and LEFT functions. In the example shown, the formula in E6 is: =SUMPRODUCT(–(LEFT(B5:B11,2)=”25″)) How this …

Continue Reading

Sum every nth column in Excel

by

This tutorial shows how to Sum every nth column in Excel using the example below; Formula =SUMPRODUCT(–(MOD(COLUMN(range)-COLUMN(range.first)+1,n)=0),range) Explanation To sum every nth column, you can use a formula based on the SUMPRODUCT, MOD, and COLUMN functions. In the example shown, the formula in L5 is: =SUMPRODUCT(–(MOD(COLUMN(B5:J5)-COLUMN(B5)+1,K5)=0),B5:J5) How this formula works At the core, uses SUMPRODUCT …

Continue Reading

Count cells that are not blank in Excel

by

This tutorial shows how to Count cells that are not blank in Excel using the example below; Formula =COUNTA(range) Explanation To count the number of cells that are not blank in a range, you can use the COUNTA function. In the example shown, E5 contains this formula: =COUNTA(B4:B8) How this formula works COUNTA is fully automatic. …

Continue Reading

Count numbers third digit equals 3 in Excel

by

This tutorial shows how to Count numbers third digit equals 3 in Excel using the example below; Formula =SUMPRODUCT(–(MID(range,3,1)=”3″)) Explanation To count numbers where the third digit equals 3, you can use a formula based on the SUMPRODUCT and MID functions. In the example shown, the formula in E5 is: =SUMPRODUCT(–(MID(B5:B12,3,1)=”3″)) How this formula works …

Continue Reading

Sum formulas only in Excel

by

This tutorial shows how to Sum formulas only in Excel using the example below; Formula =SUMPRODUCT(range*ISFORMULA(range)) Explanation To sum results from formulas only, you can use the SUMPRODUCT and ISFORMULA functions. In the example shown, the formula in F6 is: =SUMPRODUCT(sales*ISFORMULA(sales)) where “sales” is the named range C5:C13, values in C5:C9 are hardcoded, and values in C10:C13 …

Continue Reading

Count cells that contain either x or y in Excel

by

This tutorial shows how to Count cells that contain either x or y in Excel using the example below; Formula =SUMPRODUCT(–((ISNUMBER(FIND(“abc”,range)) + ISNUMBER(FIND(“def”,range)))>0)) Explanation To count cells that contain either one value or another, you an either use a helper column then tally up the count, or a more complex single cell formula. Background When …

Continue Reading

Count occurrences in entire Excel Workbook

by

This tutorial shows how to Count occurrences in entire Excel Workbook using the example below; Formula =SUMPRODUCT(COUNTIF(INDIRECT(“‘”&sheets&”‘!”&range),criteria)) Explanation To count matches in entire workbook, you can use a formula based on the COUNTIF and SUMPRODUCT functions. In the example shown, the formula in D5 is: =SUMPRODUCT(COUNTIF(INDIRECT(“‘”&sheets&”‘!A1:Z10000″),B5)) where “sheets” is the named range B8:B10. Context This …

Continue Reading

Sum if cells contain either x or y in Excel

by

This tutorial shows how to Sum if cells contain either x or y in Excel using the example below; Formula =SUMPRODUCT(–((ISNUMBER(SEARCH(“cat”,range1)) + ISNUMBER(SEARCH(“rat”,range1)))>0),range2) Explanation To sum if cells contain either one text string or another (i.e. contain “cat” or “rat”) you can use the SUMPRODUCT function. Background When you sum cells with “OR” criteria, you …

Continue Reading

Count cells that contain errors in Excel

by

This tutorial shows how to Count cells that contain errors in Excel using the example below; Formula =SUMPRODUCT(–ISERR(range)) Explanation To count cells that contain errors, you can use the ISERR function, wrapped in the SUMPRODUCT function.  In the example shown, E5 cell contains this formula: =SUMPRODUCT(–ISERROR(B5:B9)) How this formula works SUMPRODUCT accepts one or more arrays, multiplies …

Continue Reading

Count rows with multiple OR criteria in Excel

by

This tutorial shows how to Count rows with multiple OR criteria in Excel using the example below; Formula =SUMPRODUCT(–((criteria1)+(criteria2)>0)) Explanation To count rows using multiple criteria across different columns – with OR logic – you can use the SUMPRODUCT function. In the example shown, the formula in H7 is: =SUMPRODUCT(–((C5:C11=”blue”)+(D5:D11=”dog”)>0)) How this formula works In the example shown, we …

Continue Reading

Sum if equal to one of many things in Excel

by

This tutorial shows how to Sum if equal to one of many things in Excel using the example below; Formula =SUMPRODUCT(SUMIF(range,things,values)) Explanation If you need to sum values when cells are equal to one of many things, you can use a formula based on the SUMIF and SUMPRODUCT functions. In the example shown, the formula in …

Continue Reading

Count cells that contain text in Excel

by

This tutorial shows how to Count cells that contain text in Excel using the example below; Formula =COUNTIF(range,”*”) Explanation To count the number of cells that contain text (i.e. not numbers, not errors, not blank), use the COUNTIF function and a wildcard. “*” is a wildcard matching any number of characters. Do you want to count …

Continue Reading

Count sold and remaining in Excel

by

This tutorial shows how to Count sold and remaining in Excel using the example below; Formula =COUNTA(range1)-COUNTA(range2) Explanation If you have a list of items, and need to count how many you have total, how many are sold, how remain, etc., you can use the COUNTA function. This can be useful if you are selling …

Continue Reading

Sum if equal to either x or y in Excel

by

This tutorial shows how to Sum if equal to either x or y in Excel using the example below; In the example, we are summing all sales from either the West OR North region. Explanation If you need to sum numbers based on other cells being equal to either one value or another (either x …

Continue Reading

Count cells that do not contain errors in Excel

by

This tutorial shows how to Count cells that do not contain errors in Excel using the example below; Formula =SUMPRODUCT(–NOT(ISERR(range))) Explanation To count the number of cells that contain errors, you can use the ISERR and NOT functions, wrapped in the SUMPRODUCT function. In the example, the active cell contains this formula: =SUMPRODUCT(–NOT(ISERR(B4:B8))) How this …

Continue Reading

Count total matches in two ranges in Excel

by

This tutorial shows how to Count total matches in two ranges in Excel using the example below; Formula =SUMPRODUCT(COUNTIF(range1,range2)) Explanation If you want to compare two ranges, and count total matches between the two ranges, you can use a formula that combines the COUNTIF and  SUMPRODUCT functions. Context Suppose you have a “master” list of some …

Continue Reading

Sum if one criteria multiple columns in Excel

by

This tutorial shows how to Sum if one criteria multiple columns in Excel using the example below; Formula =SUMPRODUCT((criteria_range=”red”)*(sum_range)) Explanation To sum multiple columns conditionally, using one criteria, you can use a formula based on the SUMPRODUCT function. In the example show, the formula in H5 is: =SUMPRODUCT((B5:B10=”red”)*(C5:E10)) How this formula works This first expression …

Continue Reading

Count cells that contain odd numbers in Excel

by

This tutorial shows how to Count cells that contain odd numbers in Excel using the example below; Formula =SUMPRODUCT(–(MOD(range,2)=1)) Explanation To count cells that contain only odd numbers, you can use a formula based on the SUMPRODUCT function together with the MOD function. In the example, the formula in cell E6 is: =SUMPRODUCT(–(MOD(rang,2)=1)) This formula …

Continue Reading

Count unique text values in a range in Excel

by

This tutorial shows how to Count unique text values in a range in Excel using the example below; Formula =SUMPRODUCT(–(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0)) Explanation If you need to count unique text values in a range, you can use a formula that uses several functions: FREQUENCY , MATCH, ROW and SUMPRODUCT. It’s also possible to use COUNTIF, as explained …

Continue Reading

Sum matching columns in Excel

by

This tutorial shows how to sum matching columns in Excel using the example below; Formula =SUMPRODUCT(data*(headers=A1)) Explanation To sum values in columns by matching matching column headers, you can use a formula based on the SUMPRODUCT function. In the example shown, the formula in J5 is: =SUMPRODUCT(data*(LEFT(headers)=J4)) where “data” is the named range C5:G14, and “headers” is the …

Continue Reading

Count cells that contain numbers in Excel

by

This tutorial shows how to Count cells that contain numbers in Excel using the example below; Formula =COUNT(range) Explanation To count the number of cells that are not blank, use the COUNT function. In the example, the active cell contains this formula: =COUNT(B4:B8) How this formula works The COUNT function is fully automatic. It counts the …

Continue Reading

Count unique values in a range with COUNTIF in Excel

by

This tutorial shows how to Count unique values in a range with COUNTIF in Excel. using the example below; Formula =SUMPRODUCT(1/COUNTIF(data,data)) Explanation If you need to count the number of unique values in a range of cells (named “data” in the example below), you can use a formula that uses COUNTIF and SUMPRODUCT. How the …

Continue Reading

Sum top n values in Excel

by

This tutorial shows how to Sum top n values in Excel using the example below; To sum the top values in a range, you can use a formula based on the LARGE function, wrapped inside the SUMPRODUCT function. Formula =SUMPRODUCT(LARGE(range,{1,2,N})) Explanation In the generic form of the formula (above), a range of cells that contain numeric values …

Continue Reading

Count dates by day of week in Excel

by

This tutorial shows how to Count dates by day of week in Excel using the example below; Formula =SUMPRODUCT(–(WEEKDAY(dates)=day_num)) Explanation To count dates by weekday (i.e. count Mondays, Tuesdays, Wednesdays, etc.), you can use the SUMPRODUCT function together with the WEEKDAY function. In the example shown, the formula in F4 is: =SUMPRODUCT(–(WEEKDAY(dates,2)=G4)) Note: “dates” is the named …

Continue Reading

Count visible rows only with criteria in Excel

by

This tutorial shows how to Count visible rows only with criteria in Excel using the example below; Formula =SUMPRODUCT((range=criteria)*(SUBTOTAL(3,OFFSET(range,rows,0,1)))) Explanation To count visible rows only with criteria, you can use a rather complex formula based on SUMPRODUCT, SUBTOTAL, and OFFSET. The problem The SUBTOTAL function can easily generate sums and counts for hidden and non-hidden …

Continue Reading

Sum top n values with criteria in Excel

by

This tutorial shows how to Sum top n values with criteria in Excel using the example below; Formula =SUMPRODUCT(LARGE((range=criteria)*(values),{1,2,3,N})) Explanation To sum the top n values in a range matching criteria, you can use a formula based on the LARGE function, wrapped inside the SUMPRODUCT function. In the generic form of the formula (above), range represents a range of …

Continue Reading

Count dates in given year in Excel

by

This tutorial shows how to Count dates in given year in Excel using the example below; Formula =SUMPRODUCT(–(YEAR(dates)=year)) Explanation To count dates in a given year, you can use the SUMPRODUCT and YEAR functions. In the example shown, the formula in E5 is: =SUMPRODUCT(–(YEAR(dates)=D5)) where “dates” the a named range B5:B15. How this formula works The YEAR …

Continue Reading

SUMPRODUCT count multiple OR criteria in Excel

by

This tutorial shows how to work SUMPRODUCT count multiple OR criteria in Excel using the example below; Formula =SUMPRODUCT(ISNUMBER(MATCH(range1,{“A”,”B”},0))*ISNUMBER(MATCH(range2,{“X”,”Y”,”Z”},0))) Explanation To count matching rows with multiple OR criteria, you can use a formula based on the SUMPRODUCT function. In the example shown, the formula in F10 is: =SUMPRODUCT(ISNUMBER(MATCH(B5:B11,{“A”,”B”},0))* ISNUMBER(MATCH(C5:C11,{“X”,”Y”,”Z”},0))) This formula returns a count of rows …

Continue Reading

SUMPRODUCT with IF in Excel

by

This tutorial shows how to SUMPRODUCT with IF in Excel using the example below; Formula =SUMPRODUCT(–(color=”red”),quantity,price) Explanation To filter results of SUMPRODUCT with specific criteria, you can apply simple logical expressions directly to arrays in the function, instead of using the IF function. In the example shown, the formula in H5 is: =SUMPRODUCT(–(color=”red”),quantity,price) Named ranges The example …

Continue Reading

Count if row meets internal criteria in Excel

by

This tutorial shows how to Count if row meets internal criteria in Excel using the example below; To count rows in a table that meet internal, calculated criteria, without using a helper column, you can use the SUMPRODUCT function. Formula =SUMPRODUCT(–(logical_expression)) Explanation Context Imagine you have a table of sales figures for several products. You …

Continue Reading

Sum 2d range with multiple criteria in Excel

by

This tutorial shows how to Sum 2d range with multiple criteria in Excel using the example below; To lookup and sum values in a 2D range with multiple criteria, you can use the SUMPRODUCT function. Formula =SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2)) Explanation In the example shown, the formula in J6 is: =SUMPRODUCT(data*(codes=J4)*(days=J5)) using named ranges as shown in the …

Continue Reading

Weighted average in Excel

by

This tutorial shows how to work Weighted average in Excel using the example below; Formula =SUMPRODUCT(numbers,weights)/SUM(weights) Explanation To calculated a weighted average, you can use the SUMPRODUCT function together with the SUM function. In the example shown, the formula in G7 is: =SUMPRODUCT(C5:C11,D5:D11)/SUM(D5:D11) How this formula works The SUMPRODUCT function multiples arrays together and sums the result. In …

Continue Reading

Count if row meets multiple internal criteria in Excel

by

This tutorial shows how to Count if row meets multiple internal criteria in Excel using the example below; Formula =SUMPRODUCT((logical1)*(logical2)) Explanation To count rows in a table that meet multiple criteria, some of which depends on logical tests that work at the row-level, you can use the SUMPRODUCT function. Context You have a table that …

Continue Reading

Sum bottom n values in Excel

by

This tutorial shows how to Sum bottom n values in Excel. You can use a combination of SUMPRODUCT function and SMALL function to get the sum bottom  n values in the example below; Formula =SUMPRODUCT(SMALL(range,{1,2,n})) Explanation If you need to sum or add the bottom values in a range, you can do so with a formula that uses the …

Continue Reading

Count missing values in Excel

by

This tutorial shows how to calculate Count missing values in Excel using the example below; Formula =SUMPRODUCT(–(COUNTIF(list1,list2)=0)) Explanation To count the values in one list that are missing from another list, you can use a formula based on the COUNTIF and SUMPRODUCT functions. In the example shown, the formula in H6 is: =SUMPRODUCT(–(COUNTIF(list1,list2)=0)) Which returns …

Continue Reading

Posts navigation

  • 1
  • 2
  • Next

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • How to use Excel NOT Function
  • IF with boolean logic in Excel
  • SWITCH function example in Excel
  • Complete List of Excel Logical Functions, References and Examples
  • XOR function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Next biweekly payday from date in Excel
  • Basic timesheet formula with breaks in Excel
  • YEAR function: Description, Usage, Syntax, Examples and Explanation
  • Get days before a date in Excel
  • Get age from birthday in Excel

Grouping

  • Categorize text with keywords in Excel
  • Map inputs to arbitrary values in Excel
  • How to randomly assign people to groups in Excel
  • Group numbers at uneven intervals in Excel
  • Group arbitrary text values in Excel

General

  • How to calculate percent sold in Excel
  • How to Insert Cells, Row and Rows in Excel
  • Count cells less than in Excel
  • Lock Cells in a Worksheet Excel
  • How to count total number of cells in a rectangular range in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning