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 …
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 …
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 …
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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 …
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 …
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 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 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 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 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 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 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 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 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 …
What is SUMSQ function in Excel? SUMSQ function is one of the Math and Trig functions in Microsoft Excel that multiplies corresponding components in the given arrays, and returns the sum of those products. Syntax of SUMSQ function SUMPRODUCT(array1, [array2], [array3], …) The SUMPRODUCT function syntax has the following arguments: Array1 Required. The first array argument …
To return TRUE when all cells in a range are blank or empty, you can use a formula based on SUMPRODUCT and a logical test that suits the use case. Formula =SUMPRODUCT(–(range<>””))=0 Explanation In the example shown, the formula in E5 is: =SUMPRODUCT(–(B5:D5<>””))=0 How this formula works Working from the inside out, this formula contains …
To verify that multiple cells have the same value with a case-sensitive formula, you can use a simple array formula based on the EXACT and AND functions. See example below: Formula {=AND(EXACT(range,value))} Explanation In the example shown, the formula in G5 is: =AND(EXACT(B5:F5,B5)) This is an array formula and must be entered with control + …
To test if a range contains any values (i.e. at least one value) not in another range, you can use the SUMPRODUCT function with MATCH and ISNA. The MATCH function receives a single lookup value, and returns a single match if any. In this case, however, we are giving MATCH an array for lookup value, …
To test a range for numbers, you can use a formula based on the ISNUMBER and SUMPRODUCT functions. See example below: Formula =SUMPRODUCT(–ISNUMBER(range))>0 Explanation In the example shown, the formula in xxxx is: =SUMPRODUCT(–ISNUMBER(C5:C9))>0 How this formula works Working from the inside out, the ISNUMBER function will return TRUE when given a number and FALSE if …
To calculate required currency denominations, given a specific amount, you can build a currency calculation table as shown in the example. This solution uses the INT and SUMPRODUCT functions. Formula =INT((amount-SUMPRODUCT(denoms,counts))/currentdenom) Explanation In the example show, the formula in D5 is: =INT(($B5-SUMPRODUCT($C$4:C$4,$C5:C5))/D$4) How this formula works To start off, the formula in C5 is: =INT($B5/C$4) This formula divides …
To count how many times a specific a word (or any substring) appears inside a range of cells, you can use a formula based on the SUBSTITUTE, LEN, and SUMPRODUCT functions. Formula =SUMPRODUCT((LEN(range)-LEN(SUBSTITUTE(range,text,””)))/LEN(text)) Explanation In the example shown, the formula in C11 is: =SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,””)))/LEN(C2)) Note: The formula on this page counts instances of a word in a …
If you need to count specific characters in a range of cells, you can do so with a formula that uses LEN and SUBSTITUTE, along with the SUMPRODUCT function. Formula =SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(range,text,””))) Explanation In the example, the active cell contains this formula: =SUMPRODUCT(LEN(B3:B7)-LEN(SUBSTITUTE(B3:B7,”o”,””))) How the formula works For each cell in the range, SUBSTITUTE removes all …
If you want to count the total characters in a range of cells, you can do with a formula that uses LEN, along with the SUMPRODUCT function. Formula =SUMPRODUCT(LEN(rng)) Explanation In the example, the active cell contains this formula: =SUMPRODUCT(LEN(B3:B6)) Here’s how the formula works: SUMPRODUCT accepts the range B3:B6 as an array of four …