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

Data Analysis

  • What-If Analysis: Scenarios and Goal Seek in Excel
  • Randomize/ Shuffle List in Excel
  • How to calculate correlation coefficient Correlation in Excel
  • Use Data Form to input, edit and delete records in Excel
  • How to Create Thermometer Chart in Excel

References

  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Offset in Excel
  • Approximate match with multiple criteria in Excel
  • Excel Advanced Lookup using Index and Match Functions
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Excel Data validation date in specific year
  • Excel Data validation unique values only
  • Excel Data validation number multiple 100
  • Excel Data validation whole percentage only
  • Excel Data validation must begin with

Tag: SUMPRODUCT function

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 if two criteria match in Excel

by

This tutorial shows how to Count if two criteria match in Excel using the example below; Formula =COUNTIFS(range1,critera1,range2,critera2) Explanation If you want to count rows where two (or more) criteria match, you can use a formula based on the COUNTIFS function. In the example shown, we want to count the number of orders with a …

Continue Reading

3D SUMIF for multiple worksheets in Excel

by

This tutorial shows how to 3D SUMIF for multiple worksheets in Excel using the example below; Formula =SUMPRODUCT(SUMIF(INDIRECT (“‘”&sheets&”‘!”&”range”),criteria, INDIRECT(“‘”&sheets&”‘!”&”sumrange”))) Explanation To conditionally sum identical ranges that exist in separate worksheets, all in one formula, you can do so with the SUMIF function + INDIRECT, wrapped in SUMPRODUCT. In the example, the formula looks like …

Continue Reading

Count cells equal to case sensitive in Excel

by

This tutorial shows how to Count cells equal to case sensitive in Excel using the example below; Formula =SUMPRODUCT((–EXACT(value,range))) Explanation To count cells that contain certain text in a case-sensitive manner, you can use a formula that uses the EXACT function along with SUMPRODUCT. In the example, there is a list of names in a …

Continue Reading

Count long numbers without COUNTIF in Excel

by

This tutorial shows how to Count long numbers without COUNTIF in Excel using the example below; Formula =SUMPRODUCT(–(A:A=A1)) Explanation This is an annoyingly long introduction, but the context is important, sorry! If you try to count very long numbers (16+ digits) in a range with COUNTIF, you may see incorrect results, due to a bug …

Continue Reading

Sum by month ignore year in Excel

by

This tutorial shows how to Sum by month ignore year in Excel using the example below; Formula =SUMPRODUCT((MONTH(dates)=month)*amounts) Explanation To sum data by month, ignoring year, you can use a formula based on the SUMPRODUCT and MONTH functions. In the example shown, the formula in H6 is: =SUMPRODUCT((MONTH(dates)=3)*amounts) The result is a total of all …

Continue Reading

Count cells equal to one of many things

by

This tutorial shows how to Count cells equal to one of many things using the example below; Formula =SUMPRODUCT(COUNTIF(range,things)) Explanation To count the number of cells equal to one of many values, you can use the COUNTIF function inside of SUMPRODUCT. In the example shown, cell G5 contains this formula: =SUMPRODUCT(COUNTIF(B5:B10,things)) Note COUNTIF is not case-sensitive. …

Continue Reading

Count matches between two columns in Excel

by

This tutorial shows how to Count matches between two columns in Excel using the example below; Formula =SUMPRODUCT(–(range1=range2)) Explanation If you want to compare two columns and count matches in corresponding rows, you can use the SUMPRODUCT function with a simple comparison of the two ranges. For example, if you have values in B5:B11 and …

Continue Reading

Sum columns based on adjacent criteria in Excel

by

This tutorial shows how to Sum columns based on adjacent criteria in Excel using the example below; Formula =SUMPRODUCT(–(range1=criteria),range2) Explanation To sum or subtotal columns based on criteria in adjacent columns, you can use a formula based on the SUMPRODUCT function. In the example shown, the formula in L5 is: =SUMPRODUCT(–($B5:$H5=J$4),$C5:$I5) How this formula works …

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

Posts navigation

  • Previous
  • 1
  • 2

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

  • IF function: Description, Usage, Syntax, Examples and Explanation
  • Excel If, Nested If, And/Or Criteria Examples
  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • Complete List of Excel Logical Functions, References and Examples
  • Check multiple cells are equal in Excel

Date Time

  • Basic timesheet formula with breaks in Excel
  • Convert Excel time to decimal minutes
  • Get days before a date in Excel
  • Convert Excel time to Unix time in Excel
  • Check If Two Dates are same month in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • Group times into unequal buckets in Excel
  • Group numbers at uneven intervals in Excel
  • Group numbers with VLOOKUP in Excel
  • How to randomly assign people to groups in Excel

General

  • Print Excel Sheet In Landscape Or Portrait
  • 3D SUMIF for multiple worksheets in Excel
  • How to choose page/paper size in Excel before Printing
  • How to fill cell ranges with random text values in Excel
  • How to get amount with percentage in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning