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 …
If you want to count the total words in a range of cells, you can do with a formula that uses LEN and SUBSTITUTE, along with the SUMPRODUCT function. Formula =SUMPRODUCT(LEN(TRIM(range))-LEN(SUBSTITUTE(range,” “,””))+1) Note: The formula inside SUMPRODUCT will return 1 even if a cell is empty. If you need to guard against this problem, you can …
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 …
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 …
This tutorial shows how to Count holidays between two dates in Excel using example below. To count holidays that occur between two dates, you can use the SUMPRODUCT function. Formula =SUMPRODUCT((holidays>=start)*(holidays<=end)) Explanation In the example shown, the formula in F8 is: =SUMPRODUCT((B4:B12>=F5)*(B4:B12<=F6)) How this formula works This formula uses two expressions in a single array …
This tutorial shows how to Count day of week between dates in Excel using example below. To count the number of Mondays, Fridays, Sundays, etc. between two dates you can use an array formula that uses several functions: SUMPRODUCT, WEEKDAY, ROW, and INDIRECT. Formula =SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT(date1&”:”&date2)))=dow)) Explanation In the example shown, the formula in cell E6 …
This tutorial shows how to Count dates in current month in Excel using example below. To count dates in the current month, you can use a formula based on the COUNTIFS or SUMPRODUCT function as explained below. Formula =COUNTIFS(range,”>=”&EOMONTH(TODAY(),-1)+1, range,”<“&EOMONTH(TODAY(),0)+1) Explanation In the example shown above, the formula in E7 is: =COUNTIFS(dates,”>=”&EOMONTH(TODAY(), -1)+1,dates,”<“&EOMONTH(TODAY(),0)+1) Where “dates” …
To calculate work hours between two dates with a custom schedule, you can use a formula based on the WEEKDAY and SUMPRODUCT functions, with help from ROW, INDIRECT, and MID. Formula =SUMPRODUCT(MID(schedule,WEEKDAY (ROW(INDIRECT(start&”:”&end))),1)*ISNA (MATCH(ROW(INDIRECT(start&”:”&end)),holidays,0))) Explanation In the example shown, the formula in F8 is: =SUMPRODUCT(MID(D6,WEEKDAY(ROW (INDIRECT(B6&”:”&C6))),1)*ISNA(MATCH (ROW(INDIRECT(B6&”:”&C6)),holidays,0))) Which returns 36 hours, based on a custom …
To sum the total amount of time over 30 minutes, given a set of times that represent duration, you can use the SUMPRODUCT and TIME functions. Alternatively, use SUMIFS and COUNTIFS functions. Formula =SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0))) Explanation In the example shown, the formula in G5 is: =SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0))) where “times” is the named range C5:C14. How this formula works This formula …
If you want to test a cell to see if it contains all items in a list, you can do so with a formula that uses the SEARCH function, with help from the ISNUMBER, SUMPRODUCT, and COUNTA functions. Case study: Let’s say you have a list of text strings in the range B5:B8, and you want …
This tutorial shows how to check if cell contains one of many things in Excel using example below: If you want to test a cell to see if it contains one of several things, you can do so with a formula that uses the SEARCH function, with help from the ISNUMBER and SUMPRODUCT functions. Formula =SUMPRODUCT(–ISNUMBER(SEARCH(things,A1)))>0 …
To test a cell for one of many strings, while excluding others, you can use a formula based on the SEARCH, ISNUMBER, and SUMPRODUCT functions. Formula =(SUMPRODUCT(–ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(–ISNUMBER(SEARCH(exclude,A1)))=0) Note: this formula returns either 1 or zero, which are handled like TRUE and FALSE in formulas, conditional formatting, or data validation. Explanation In the example shown the …
If you want to test a cell to see if it equals one of several things, you can do so with a formula that uses the SUMPRODUCT function. Case study: Let’s say you have a list of text strings in the range B5:B11, and you want to test each cell against another list of things …
To count the number of specific words or keywords that appear in a given cell, you can use a formula based on the SEARCH, ISNUMBER, and SUMPRODUCT functions. Formula =SUMPRODUCT(–ISNUMBER(SEARCH(keywords,A1))) Note: if a keyword appears more than once in a given cell, it will only be counted once. In other words, the formula only counts …
This tutorial show how to Count birthdays by month in Excel using the example below. To count the number of birthdays in a list, you can use a formula based on the SUMPRODUCT and MONTH functions. Formula =SUMPRODUCT(–(MONTH(birthday)=number)) Explanation of how this formula works In the example shown, E5 contains this formula: =SUMPRODUCT(–(MONTH(birthday)=D5)) This formula counts …
To calculate and display the days, hours, and minutes between two dates, you can use the TEXT function with a little help from the INT function. Alternatively, you can adapt the formula using SUMPRODUCT. Formula =INT(end-start)&” days “&TEXT(end-start,”h”” hrs “”m”” mins “””) Explanation In the example shown, the formula in D5 is: =INT(C5-B5)&” days “&TEXT(C5-B5,”h”” hrs …
This tutorial shows how to Highlight cells that contain one of many in Excel using the example below; Formula =SUMPRODUCT(–ISNUMBER(SEARCH(things,A1)))>0 Explanation To highlight cells that contain one of many text strings, you can use a formula based on the functions ISNUMBER and SEARCH, together with the SUMPRODUCT function. In the example shown, the conditional formatting applied …
This tutorial shows how to Highlight duplicate rows in Excel using the example below; Formula =COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1) Explanation Excel contains a built-in preset for highlighting duplicate values with conditional formatting, but it only works at the cell level. If you want to highlight entire rows that are duplicates you’ll need to use your own formula, as explained …
This tutorial shows how to Highlight duplicate columns in Excel using the example below; Formula =SUMPRODUCT((row1=ref1)*(row2=ref2)*(row3=ref3))>1 Explanation Excel contains a built-in preset for highlighting duplicate values with conditional formatting, but it only works at the cell level. If you want to find and highlight duplicate columns, you’ll need to use your own formula, as explained …
Set criteria to reject specific data in Excel To disallow input that contains one of many things, you can use a custom data validation rule based the SEARCH function. Formula =SUMPRODUCT(–ISNUMBER(SEARCH(list,A1)))=0 Explanation In the example shown above, the data validation applied to B5:B11 is: =SUMPRODUCT(–ISNUMBER(SEARCH(list,B5)))=0 Note: Cell references in data validation formulas are relative to …
This tutorial shows how to calculate IF with boolean logic in Excel using the example below; Formula = IF(criteria1*criteria2*criteria3,result) Explanation In the example shown, the formula in F8 is: {=SUM(IF((color=”red”)*(region=”East”)*(quantity>7),quantity))} Note: this is an array formula, and must be entered with control + shift + enter. How this formula works Note: This example demonstrates how to replace a nested …
This tutorial shows how to work Conditional formatting dates overlap in Excel using the example below; Formula =SUMPRODUCT((start_date<=end_dates)*(end_date>=start_dates))>1 Explanation To highlight cells where dates overlap you can use conditional formatting with a formula based on the SUMPRODUCT function. In the example shown the formula in south E6 is: =SUMPRODUCT(($C6<=$D$5:$D$9)*($D6>=$C$5:$C$9))>1 This is the same formula used to highlight entire …
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 …
This tutorial shows how to calculate Exact match lookup with SUMPRODUCT in Excel using the example below; Formula =SUMPRODUCT(–(EXACT(val,lookup_col)),result_col) Explanation Case sensitive lookups in Excel By default, standard lookups in Excel are not case-sensitive. Both VLOOKUP and INDEX/MATCH will simply return the first match, ignoring case. A direct way to workaround this limitation, is to use an array formula …
This tutorial shows how to Get location of value in 2D array in Excel using the example below; Formula =SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 Explanation To locate the position of a value in a 2D array, you can use the SUMPRODUCT function. In the example shown, the formulas used to locate the row and column numbers of the max value …
This tutorial shows how to Sum lookup values using SUMIF in Excel using the example below; Formula =SUMPRODUCT(SUMIF(codes,lookups,values)) Explanation To sum values retrieved by a lookup operation, you can use SUMPRODUCT with the SUMIF function. In the example shown, the formula in H5 is: =SUMPRODUCT(SUMIF(codes,C5:G5,values)) Where codes is the named range J4:J5 and values is …
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 …