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 whose components you want to…
Tag: SUMPRODUCT function
Cash denomination calculator in Excel
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 the amount in column B…
How to test a range for numbers in Excel
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 not. When you supply a…
Check if multiple cells have same value with case sensitive in Excel
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 + shift + enter How this…
Check if range contains a value not in another range in Excel
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, so it will return an…
How to return TRUE when all cells in range are blank in Excel
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 an expression inside SUMPRODUCT that…
How to count specific words in a range in Excel
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 range. For example, if a cell…
How to count total characters in a range in Excel
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 cells. For each cell in…
How to count specific characters in a range in Excel
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 the o’s from the text,…
How to count total words in a range in Excel
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 add another array to SUMPRODUCT…
Get work hours between dates custom schedule in Excel
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 schedule where 8 hours are…
Two ways to sum time over 30 minutes in Excel
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 uses the SUMPRODUCT function to…
How to check cell that contains one of many with exclusions in Excel
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 formula in C5 is: =(SUMPRODUCT(–ISNUMBER(SEARCH(include,B5)))>0)…
How to check if cell contains one of many things in Excel
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 Explanation Context Let’s say you…
How to check if cell contains all of many things in Excel
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 to find out if these…
How to check cell equals one of many things in Excel
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 in range E5:E9. In other…
How to count keywords in a range of cell
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 instances of different keywords. Explanation…
Count holidays between two dates in Excel
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 inside the SUMPRODUCT function. The…
Count day of week between dates in Excel
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 is =SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT(B6&”:”&C6)))=D6)) How this formula…
Count dates in current month in Excel
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” is the named range B5:B104. How this…
Count birthdays by month in Excel
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 birthdays in January (since D5…
Get days, hours, and minutes between dates in Excel
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 “”m”” mins “””) How this…
Excel Data validation must not contain
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 the upper left cell in…
Highlight duplicate columns in Excel
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 below. To highlight duplicate columns,…
Highlight duplicate rows in Excel
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 below. If you want to…
Highlight cells that contain one of many in Excel
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 to B4:B11 is based on…
Conditional formatting dates overlap in Excel
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 rows in the table using…
IF with boolean logic in Excel
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 IF formula with a single IF…
Sum lookup values using SUMIF in Excel
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 the named range K4:K5. Context…
Get location of value in 2D array in Excel
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 in the array are: =SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1…
Exact match lookup with SUMPRODUCT in Excel
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 based on INDEX/MATCH with EXACT.…
Count missing values in Excel
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 1 since the value “Osborne”…
Weighted average in Excel
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 case, SUMPRODUCT returns the total…
Count cells equal to one of many things
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. How this formula works COUNTIF…
Count cells equal to case sensitive in Excel
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 named range (“names”), B3:B9. In…
Count cells that contain text in Excel
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 cells that contain specific text? See this formula…
Count cells not equal to many things in Excel
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)))) where “data” is the named range B5:B16…
Count cells that contain errors in Excel
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 the arrays together, and returns…
Count cells that contain odd numbers in Excel
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 returns 4 since there are…
Count cells that contain numbers in Excel
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 number of cells in the…