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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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. …
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 …
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 …
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)))) …
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: …
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 …
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 …
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 …
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 …
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. …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …