*The tutorial explains the basic and advanced uses of the SUMPRODUCT function in Excel. You will find a number of formula examples to compare arrays, conditionally sum and count cells with multiple criteria, calculate a weighted average and more. *

When you hear the name of SUMPRODUCT for the first time, it may sound like some useless formula that performs an ordinary sum of the products operation. But that definition does not show even a tiny fraction of what Excel SUMPRODUCT is capable of.

In fact, SUMPRODUCT is a remarkably versatile function with many uses. Due to its unique ability to handle arrays in smart and elegant ways, SUMPRODUCT is extremely useful, if not indispensable, when it comes to comparing data in two or more ranges and calculating data with multiple criteria. The following examples will reveal the full power of SUMPRODUCT and its effectiveness will become crystal clear.

Technically, the SUMPRODUCT function in Excel multiplies the numbers in the specified arrays, and returns the sum of those products.

The syntax of the SUMPRODUCT function is simple and straightforward:

SUMPRODUCT(array1, [array2], [array3], …)

Where array1, array2, etc. are continuous ranges of cells or arrays whose elements you want to multiply, and then add.

The minimum number of arrays is 1. In this case, a SUMPRODUCT formula simply adds up all of the array elements and returns the sum.

The maximum number of arrays is 255 in Excel 2016, Excel 2013, Excel 2010, and Excel 2007, and 30 in earlier Excel versions.

Although SUMPRODUCT works with arrays, it does not require using the array shortcut (Ctrl + Shift + Enter). You compete a SUMPRODUCT formula in a usual way by pressing the Enter key.

- All arrays in a SUMPRODUCT formula must have the same number of rows and columns, otherwise you get the #VALUE! error.
- If any array argument contains non-numeric values, they will be treated as zeros.
- If an array is a logical test, it results in TRUE and FALSE values. In most cases, you'd need to convert them to 1 and 0 by using the double unary operator (--) . Please see the SUMPRODUCT with multiple criteria example for more details.
- SUMPRODUCT does not support wildcard characters.

To gain a general understanding of how the Excel SUMPRODUCT function works, consider the following example.

Supposing you have quantity in cells A2:A4, prices in cells B2:B4, and you wish to find out the total. If you were doing a school math test, you would multiply the quantity by price for each item, and then add up the subtotals. In Microsoft Excel, you can get the result with a single SUMPRODUCT formula:

`=SUMPRODUCT(A2:A4,B2:B4)`

The following screenshots shows it in action:

Here is what's going on under the hood in terms of math:

- The formula takes the 1
^{st}number in the 1^{st}array and multiplies it by the 1^{st}number in the 2^{nd}array, then takes the 2^{nd}number in the 1^{st}array and multiplies it by the 2^{nd}number in the 2^{nd}array, and so on. - When all of the array elements are multiplied, the formula adds up the products and returns the sum.

In other words, our SUMPRODUCT formula performs the following mathematical operations:

`=A2*B2 + A3*B3 + A4*B4`

Just think how much time it could save you if your table contained not 3 rows of data, but 3 hundred or 3 thousand rows!

Multiplying two or more ranges together and then summing the products is the simplest and most obvious usage of SUBTOTAL in Excel, though not by far the only one. The real beauty of the Excel SUMPRODUCT function is that it can do far more than its stated purpose. Further on in this tutorial, you will find a handful of formulas that demonstrate more advanced and exciting uses, so please keep reading.

Usually in Microsoft Excel, there is more than one way to accomplish the same task. But when it comes to comparing two or more arrays, especially with multiple criteria, SUMPRODUCT is the most effective, if not the only, solution. Well, either SUMPRODUCT or array formula.

Assuming you have a list of items in column A, planned sale figures in column B, and actual sales in column C. Your goal is to find out how many items have made less sales than planned. For this, use one of the following variations of the SUMPRODUCT formula:

`=SUMPRODUCT(--(C2:C10<B2:B10))`

or

`=SUMPRODUCT((C2:C10<B2:B10)*1)`

Where C2:C10 are real sales and B2:B10 are planned sales.

But what if you had more than one condition? Let's say, you want to count how many times *Apples* performed worse than planned. The solution is to add one more criterion to the SUMPRODUCT formula:

`=SUMPRODUCT(--(C2:C10<B2:B10), --(A2:A10="apples"))`

Or, you can use the following syntax:

`=SUMPRODUCT((C2:C10<B2:B10)*(A2:A10="apples"))`

And now, let's take a minute and understand what the above formulas are actually doing. I believe it is a worthy time investment because many other SUMPRODUCT formulas work with the same logic.

For starters, let's break down a simpler formula that compares numbers in 2 columns row-by-row, and tells us how many times column C is less than column B:

`=SUMPRODUCT(--(C2:C10<B2:B10))`

If you select the portion (C2:C10<B2:B10) in the formula bar, and press F9 to view the underlying values, you will see the following array:

What we have here is an array of Boolean values TRUE and FALSE, where TRUE means the specified condition is met (i.e. a value in column C is less than a value in column B in the same row), and FALSE signifies the condition is not met.

The double negative (--), which is technically called the **double unary operator**, coerces TRUE and FALSE into ones and zeros: {0;1;0;0;1;0;1;0;0}.

Another way to convert the logical values into the numeric values is multiple the array by 1:

`=SUMPRODUCT((C2:C10<B2:B10)*1)`

Either way, since there is just one array in the SUMPRODUCT formula, it simply adds up 1's in the resulting array and we get the desired count. Easy, isn't it?

When an Excel SUMPRODUCT formula contains two or more arrays, it multiplies the elements of all the arrays, and then adds up the results.

As you may remember, we used the following formulas to find out how many times the number of real sales (column C) was less than planned sales (column B) for *Apples* (column A):

`=SUMPRODUCT(--(C2:C10<B2:B10), --(A2:A10="apples"))`

or

`=SUMPRODUCT((C2:C10<B2:B10)*(A2:A10="apples"))`

The only tech difference between the formulas is the method of coercing TRUE and FALSE into 1 and 0 - by using the double unary or multiplication operation. As the result, we get two arrays of ones and zeros:

The multiplication operation performed by SUMPRODUCT joins them into a single array. And since multiplying by zero always gives zero, 1 appears only when both conditions are met, and consequently only those rows are counted:

In Excel 2003 and older versions that did not have the so-called IFs functions, one of the most common uses of the SUMPRODUCT function was to conditionally sum or count cells with multiple criteria. Beginning with Excel 2007, Microsoft introduced a series of functions specially designed for such tasks - SUMIFS, COUNTIFS and AVERAGEIFS.

But even in the modern versions of Excel, a SUMPRODUCT formula could be a worthy alternative, for example, to conditionally sum and count cells with the OR logic. Below you will find a few formula examples that demonstrate this ability in action.

Supposing you have the following dataset, where column A lists the regions, column B - items and column C - sales figures:

What you want is get the count, sum and average of *Apples* sales for the *North* region.

In the recent versions of Excel 2016, 2013, 2010 and 2007, the task can be easily accomplished by using a SUMIFS, COUNTIFS and AVERAGEIFS formula. If you are not looking for easy ways, or if you are still using Excel 2003 or older, you can get the desired result with SUMPRODUCT.

- To
**count***Apples*sales for*North*:`=SUMPRODUCT(--(A2:A12="north"), --(B2:B12="apples"))`

or

`=SUMPRODUCT((A2:A12="north")*(B2:B12="apples"))`

- To
**sum***Apples*sales for*North:*`=SUMPRODUCT(--(A2:A12="north"), --(B2:B12="apples"), C2:C12)`

or

`=SUMPRODUCT((A2:A12="north")*(B2:B12="apples")*C2:C12)`

- To
**average***Apples*sales for*North:*To calculate the average, we simply divide Sum by Count like this:

`=SUMPRODUCT(--(A2:A12="north"), --(B2:B12="apples"), C2:C12) / SUMPRODUCT( --(A2:A12="north"), --(B2:B12="apples"))`

To add more flexibility to your SUMPRODUCT formulas, you can specify the desired *Region* and *Item* in separate cells, and then reference those cells in your formula like shown in the screenshot below:

From the previous example, you already know how the Excel SUMPRODUCT formula counts cells with multiple conditions. If you understand that, it will be very easy for you to comprehend the sum logic.

Let me remind you that we used the following formula to sum *Apples* sales in the *North* region:

`=SUMPRODUCT(--(A2:A12="north"), --(B2:B12="apples"), C2:C12)`

An intermediate result of the above formula are the following 3 arrays:

- In the 1
^{st}array, 1 stands for*North*, and 0 for any other region. - In the 2
^{nd}array, 1 stands for*Apples*, and 0 for any other item. - The 3
^{rd}array contains the sales numbers exactly as they appear in cells C2:C12.

Remembering that multiplying by 0 always gives zero, and multiplying by 1 gives the same number, we get the final array consisting of the sales numbers and zeros - a sales number appears only if the first two arrays have 1 in the same position, i.e. both of the specified conditions are met; zero otherwise:

Adding up the numbers in the above array delivers the desired result - the total of the *Apples* sales in the *North* region.

To conditionally sum or count cells with the OR logic, use the **plus symbol** (+) in between the arrays.

In Excel SUMPRODUCT formulas, as well as in array formulas, the plus symbol acts like the OR operator that instructs Excel to return TRUE if ANY of the conditions in a given expression evaluates to TRUE.

For example, to get the **count** of all *Apples* and *Lemons* sales regardless of the region, use this formula:

`=SUMPRODUCT((B2:B12="apples")+(B2:B12="lemons"))`

Translated into plain English, the formula reads as follows: *Count cells if B2:B12="apples" OR B2:B12="lemons"*.

To **sum** *Apples* and *Lemons* sales, add one more argument containing the *Sales* range:

`=SUMPRODUCT((B2:B12="apples")+(B2:B12="lemons"), C2:C12)`

The following screenshot shows a similar formula in action:

In many situations, you might need to conditionally count or sum cells with AND logic and OR logic at a time. Even in the latest versions of Excel, the IFs series of functions is not capable of that.

One of the possible solutions is combining two or more functions SUMIFS + SUMIFS or COUNTIFS + COUNTIFS.

Another way is using the Excel SUMPRODUCT function where:

- Asterisk (*) is used as the AND operator.
- Plus symbol (+) is used as the OR operator.

To make things easier to understand, consider the following examples.

To **count** how many times *Apples* and *Lemons* were sold in the *North* region, make a formula with the following logic:

`=Count If ((Region="north") AND ((Item="Apples") OR (Item="Lemons")))`

Upon applying the appropriate SUMPRODUCT syntax, the formula takes the following shape:

`=SUMPRODUCT((A2:A12="north")*((B2:B12="apples")+(B2:B12="lemons")))`

To **sum ***Apples* and *Lemons* sales in the *North* region, take the above formula and add the *Sales* array with the AND logic:

`=SUMPRODUCT((A2:A12="north")*((B2:B12="apples")+(B2:B12="lemons"))*C2:C12)`

To make the formulas a bit more compact, you can type the variables in separate cells - *Region* in F1 and *Items* in F2 and H2 - and refer to those cells in your formula:

In one of the previous examples, we discussed a SUMPRODUCT formula for conditional average. Another common usage of SUMPRODUCT in Excel is calculating a weighted average where each value is assigned a certain weight.

The generic SUMPRODUCT weighted average formula is as follows:

SUMPRODUCT(*values*, *weights*) / SUM(*weights*)

Assuming that values are in cells B2:B7 and weights are in cell C2:C7, the weighted average SUMPRODUCT formula will look like this:

`=SUMPRODUCT(B2:B7,C2:C7)/SUM(C2:C7)`

I believe at this point you won't have any difficulties with understanding the formula logic. If someone needs a detailed explanation, please check out the following tutorial: Calculating weighted average in Excel.

Even if you are reading this article for informational purposes and the details are likely to fade away in your memory, remember just one key point - the Excel SUMPRODUCT function deals with arrays. And because SUMPRODUCT offers much of the power of array formulas, it can become an easy-to-use replacement for them.

What advantages does this gives to you? Basically, you will be able to manage your formulas an easy way without having to press Ctrl + Shift + Enter every time you are entering a new or editing an existing array formula.

As an example, we can take a simple array formula that counts all characters in a given range:

{=SUM(LEN(*range*))}

and turn it into a regular formula:

=SUMPRODUCT(LEN(*range*))

For practice, you can take these Excel array formulas and try to re-write then using the SUMPRODUCT function. Also, you can download the Excel SUMPRODUCT sample workbook and reverse-engineer the formulas for better understanding.

Now that you know the syntax and logic of the SUMPRODUCT function in Excel, you may want to learn more sophisticated and more powerful formulas where SUMPRODUCT is used in liaison with other Excel functions.

Excel formulas
Excel functions
Vlookup in Excel
Merge data in Excel
SumIf
Excel CountIf
Excel Compare
Excel If statement
Excel Charts
Pivot Table
Updates
Excel conditional formatting
Excel formatting
Excel time
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Outlook templates

## 94 Responses to "Excel SUMPRODUCT function with formula examples"

hi Svetlana,

thank you for sharing your experince of excel.

thats great.

one of the other usage of sumproduct function is

to limit user to enter data in column A only using character (without contain number);

1-select column A

enter this formula in the costum section at data validation.

=sumproduct(--isnumber(--mid(a1;row(indirect("1:"&len(A1)));1)))=0

I want to transfer date to another sheet automatically to specific sheet with specific data

I fill all data in sheet 1 and split this data to others sheet 1, sheet 2 , sheet 3 ....

All i need is to transfer date because i recall the others data from sheet 1by using array function SUMPRODUCT, SUMIFS.

My header including,

Date Tag Fueld Hour/Km

I use SUMPRODUCT function to recall the Tag, Fueld and Hour/Km from main sheet 1 by manual filling the date.

Please help me on this situation or would be appreciate for another way.

Sorry for my English!

Thanks in advanced,

thuraz

Thank you for this article -- it's very helpful. I'm wondering if you could give me some advice on the following:

I’m trying to create a spreadsheet that will track staff time-off. The formulas are getting a bit complicated because they are totaling from 5 separate “hours absent” columns (one for each day of the work week) and 5 corresponding “type of time off” columns. Columns headers are in row 9. The sheet is set up so that supervisors can select a particular week (column A), an employee (column B), and then the hours absent on Monday of that week (column C) and type of time off taken on that day, e.g., PTO, vacation, jury duty, etc. (column D). Columns C and D are then basically repeated through Column L to account for each day of the week through Friday.

In the section that totals the time-off for each employee, I want a total time taken for the year, as well as a breakdown showing the total of each type of time off taken by that employee. I’ve managed the total time taken with the following:

=SUMPRODUCT(($B$10:$B$188=”Annie”)*($C$9:$L$9=”Hours Absent”),$C$10:$L$188)

However, I’m struggling with the formula for the disaggregated data. Here is what I have at the moment to disaggregate only the PTO-type of time off:

=SUMPRODUCT(($B$10:$B$188=”Annie”)*($C$9:$L$9=”Hours Absent”)*((D10:D188=”PTO”)+(F10:F188=”PTO”)+(H10:H188=”PTO”)+(J10:J188=”PTO”)+(L10:L188=”PTO”)>0),$C$10:$L$188)

It almost works, but only excludes non-PTO hours from the Monday column (column C). So, it is (correctly) NOT including Annie’s vacation-type time on Monday, but IS (incorrectly) including her vacation-type time on Tuesday through Friday.

I’m hoping I’m missing something simple and you can set me on the right path! I started out thinking this could all be easily solved with a pivot table, but can’t seem to make that work either…

Any advice would be greatly appreciated!

Amy

Hello Amy,

To make things easier for us to understand, can you please send a sample workbook to support@ablebits.com, or upload it to Excel Online and post a link here.

Hi,

I am having a problem how to work out a formula to calculate the sum of the smallest three values in a range. However the range is on alternate columns

for example

pos cat pos cat pos cat pos cat pos cat pos cat pos cat

4 2 6 4 1 1 3 3 10 10 - - - -

i wish to total the best three results in the pos columns (ignoring the blank columns - )

my result should be :

all pos results are 4,6,1,3,10 - sum of best three results(smallest) = 4+1+3 = 8

is there a formula which could perform this

many thanks for any help

I have this formula in cell F3 in my table

=SUMPRODUCT((TEXT($I:$I,"mm-yyyy")="01-2016")*1) and it works great

I would like to change the ="01-2016")*1)to reference cells in the next column so F3 would reference E3, F4 - E4 and so on but I can not seem to do it can you please help

Try this;

=SUMPRODUCT(--($I:$I=G3))

G3 is the ref cell that brings dynamical selection to the sumproduct formula

Hi there,

thank you for the web information about excel.

I have a question, how to use SUMPRODUCT to sum range of value which is positive or negtive.

thank you

Yao Yao

thnx

Hi,

Is it possible to use conditional weighted average for sales matrixes? Lets say I have few products with different sales amounts and different prices, and want to get conditional weighted average for price based on sales amount and product type.

thanks.

Hello all,

I am trying to write a formula for the following conditions in excel.

Penalties(Days) Penalty per violation

1-14 $1,000

15-30 $2,000

31-60 $3,000

61-180 $4,000

>180 $5,000

Please help me to write a formula to calculate total penalties incurred based on the number of days of delay.

Thanks

Rama

Dear Rama,

Try the function VLOOKUP:

1) make up a simple reference table of two columns - Days and Penalty;

2) in the Days column insert a number that is equal to the right border of each range plus 1:

1 1000

15 2000

31 3000

61 4000

181 5000

3) convert your reference table into a named range, for example, Days_to_Penalty;

4) use VLOOKUP(number_of_days; Days_to_Penalty; 2; 1);

5) it's the last argument (1) that makes VLOOKUP search NOT the exact values.

I'd like to sumproduct the two column,that the value are negative.

please help me.

Hello,

I'm having an issue adding multiple numbers in one cell, based on which side of the / they are on. The formula to add two different numbers separated by a / (ex 5/7) is =SUMPRODUCT(--LEFT(AA8:AA11,FIND("/",AA8:AA11)-1))&"/"&SUMPRODUCT(--REPLACE(AA8:AA11,1,FIND("/",AA8:AA11),0))

But what about if I want to change it to 3 values, like 7/4/10. How would this affect the formula?

Thanks!

I have many employees in my spread sheet , am just giving some sample date here . I have to find the min and max time for each employee and for each date . How can I do that.

Employee date time

e1 1-Jan-13 8:10

e1 2-Jan-13 8:00

e1 2-Jan-13 9:00

e1 2-Jan-13 10:00

e1 2-Jan-13 11:00

e1 1-Jan-13 16:00

E2 2-Jan-13 8:10

E2 2-Jan-13 8:00

E2 2-Jan-13 9:00

E2 2-Jan-13 10:00

E2 2-Jan-13 11:00

E2 1-Jan-13 17:00

Employee ID is in range $A$1:$A$12, date is in range $B$1:$B$12 and time is in range $C$1:$C$12.

Look up Employee ID is e2 and look up date is f2.

max time:=SUMPRODUCT(MAX((($A$1:$A$12=E2)*1)*(($B$1:$B$12=F2)*1)*$C$1:$C$12))

min time:=SUMPRODUCT(MIN(((($A$1:$A$12E2)*100)+(($A$1:$A$12=E2)*1))*((($B$1:$B$12F2)*100)+(($B$1:$B$12=F2)*1))*$C$1:$C$12))

Hi,

I have 3 sheets namely: Data, Assumption, Summary

In assumption sheet, you can see criteria 1 and corresponding criteria 2.

In Data Sheet, you can see criteria 2 and amount.

In summary Sheet, I needed Criteria 1 and amount.

Usually, I have to lookup criteria 1 from assumption and put it in data sheet. But I am not allowed to modify data sheet.

Or, =SUM(SUMIF(Data Sheet criteria 1 range,{"criteria 1,criteria 1,criteria 1,criteria 1"},Data sheet sum range)). But when I require to change assumptions, I have to update formulas.

My problem is:

1) I should not modify data sheet

2) I needed my summary to be updated for any changes made in assumptions

Please feel free to share your ideas.

Hi Concern,

Thanks a lot for made a good opportunity to know about excel. Very helpful article.

I've never been good with sumproduct, and I think it's the answer to my problem but I can't get it to work! I want to add up everything with an account number that begins with 7, easy enough except that there are spaces in front of the account and the number of spaces is always different. So I want to basically do SUMPRODUCT(LEFT(TRIM($A:$A),1)="7"),$B:$B). The amounts are in Column B and the criteria is in Column A. I can't get this to work for the life of me!!

=SUMPRODUCT((LEFT(TRIM($A:$A),1)="7")*1,$B:$B)

Hi there,

I'm using the following formula, but I want the formula to choose the same text as a cell (B20), is there a way the formula can be adapted?

=SUMPRODUCT(--(B68:B109="B20"),--(N68:N109))

B20 is an individual's name, so will look different for the next cell - currently having to manually enter in individual names.

hi, i don't think i saw an example for multiple levels. i think this will work. thanks.

=SUMPRODUCT((F9{1,3,9}))

oh well, delete something. my post got chopped.

=SUMPRODUCT((F9{1,3,9}))

geez 1 more time

=SUMPRODUCT((F9{1,3,9}))

=SUMPRODUCT

((F9{1,3,9}))

funny site not accept formulas in text..

SUMPRODUCT((F9lessthan{0.01,1,5})

ASTERISK

parenthesisMIN(M9:N9)greaterthan{1,3,9}))

Nice workaround to assist with a formula, Dave!

Thanks for your persistence! :)

Used =sumproduct(1/countif(array1,array2)) to count unique values in a column with duplicates but once table is filtered the answer doesnt change.

Hi Guys,

I need some help in excel formula

I have some data with city name and numbers so just i wanted to identify the only unique values using only formula.

Data is like that

A B

City Number

A 123

B 456

A 678

A 123

Thanks in advance!

Hello, Amol,

To highlight the unique values, please select the column with the data, then on the

Hometab, in theStylesgroup, clickConditional Formatting > Highlight Cells Rules > Duplicate Values. In the next pop-up window selectUnique, choose the colour and pressOK.You can learn more on how to apply conditional formatting for uniques or duplicates in this article.

Please note that if you want to use the formulas, the values will be either simply counted or transferred to another columns. If this is what you need, please specify.

Hi

I have a table of profit and loss information running for a whole year but I want to set up a formula that will calculate the year to date position. eg I type in June and the financial info say revenue from January to June is added together. Tried initial using an array and could do this with a basic =if function but there must be a better way.

thanks Paul

kindly tell me how to get the total of two items through Sumproduct formula...

Hi Ma'am I just want to say thank you. You are so kind and brilliant, I wish I can be like you someday. Thank you so much. You are an inspiration to me, please continue helping people like me. ^_^

Please help me to sum below 2 array...

I want to add particular value of column 4 in column 2 if condition of column 1&2 satisfy, example I want to add Jan month value to each date of Jan month.

02-01-2017 414.748 JAN 312.6446

04-01-2017 476.886 FEB 796.09325

05-01-2017 409.566 MARCH 358.3840417

06-01-2017 398.519 APRIL 241.6108077

01-02-2017 1051.862 MAY 94.96810345

03-02-2017 790.097 JULY 9.945451613

04-02-2017 563.685

05-02-2017 463.103

09-03-2017 855.292

10-03-2017 957.937

11-03-2017 1056.677

12-03-2017 1077.678

13-03-2017 1569.847

This has been super helpful! My tables have been more efficient since finding double unary operator. The only challenge is that there is not a lot of help to troubleshoot. I have a simple equation that won't calculate. When I look at the functional arguments, I don't see any errors on my arrays.

My equation: SUMPRODUCT(--('Data Input'!B4:B37='All-up Burndown Chart'!A2),'Data Input'!F3:F37)

If column B (which is a list of buildings) equals a building number identified in cell A2, then sum the fields in column F.

Any thoughts on why I'm not getting an output?

A big "Thank you" for helping me out in understanding Sumproduct function

Finding the double unary operator has been so helpful! I currently have very large files that I am creating tables for and can't get this equation to work. I am wondering if there is a row limit for the formula, since this is the largest list I am working with.

SUMPRODUCT(--('Master List'!$B$2:$B$11091=$A$1),--('Master List'!$F$2:$F$11091""),--(MONTH('Master List'!$F$2:$F$11091)='Data Table - DO NOT DELETE'!F$3),--(YEAR('Master List'!$F$2:$F$11091)='Data Table - DO NOT DELETE'!F$2))

Note, the only part of this equation that breaks when providing an answer is the month and year. I do have a formula in column F, however this shouldn't make a difference.

=SUMPRODUCT(--('Excel - Full Data'!$D$4:$D$2100="WCCI"),('Excel - Full Data'!$K$4:$K$2100>0)*('Excel - Full Data'!$K$4:$K$2100<5000)*('Excel - Full Data'!$K$4:$K$2100))

Am I able to add an Additional parameter which includes "WCCI - TS" to the "WCCI" for $D$4:$D$$2100 range?

Any help would be greatly appreciated

Hello,

If I understand your task correctly, please try the following formula:

=SUMPRODUCT((('Excel - Full Data'!$D$4:$D$2100="WCCI")+('Excel - Full Data'!$D$4:$D$2100="WCCI - TS")),('Excel - Full Data'!$K$4:$K$2100>0)*('Excel - Full Data'!$K$4:$K$2100<5000)*('Excel - Full Data'!$K$4:$K$2100))

Hope this will work for you

Hi, thank you so much, this page was a great help. I have one case that I need some help. I noticed that when I use sumproduct with an OR connection, the values provided are not TRUE or FALSE (e.g. TRUE,FALSE+TRUE,FALSE resulting in {2,0}) How can I have a TRUE/FALSE output?

I want to know in how many rows either or both of the conditions appear, and not the count of every occurance.

Thank you for your help!

hello, I found a solution, the resulting array e.g. {2,0} can be adapted like this: --{2,0}>0 to result in {1,0}

VALUE 100 500 600 700 800 900

TOTAL QTY.

RAM 10 1 2 3 4

MOHAN 24 2 3 1 6 7 5

SOHAN 31 2 3 4 6 7 9

VIMAL 28 1 1 5 6 7 8

need to value of each person wise

I have an interesting problem I cant figure out! I have a file which might make it easier but I will try to explain.

I have 4 worksheets in one workbook. Datasheet, Homeloans, YR2, Table.

On Datasheet in M2 I have a Date formatted as d-mmm-yy

On Homeloans I have Dates from A1:L1 which are derived from the date on Datasheet =DATE(YEAR(Datasheet!$M$2),MONTH(Datasheet!$M$2)+0,1) that progress up to =DATE(YEAR(Datasheet!$M$2),MONTH(Datasheet!$M$2)+11,1) in L1

In A2:L2 I have values entered

On YR2 I have the same setup. The date system is setup to look like a fiscal year instead of a calendar year.

On the Table sheet from B1:Y1 I have dates. B1 uses the formula =IF(Datasheet!M2"",TEXT(Datasheet!M2,"d-mmm-yy"),"Jan") B2 uses =IF(OR(B$1="",B$1=""),"",TEXT(EDATE(B$1,1),"d-mmm-yy")) and is propagated to Y1.

In B2:Y2 I am using this formula =SUMPRODUCT(--(TEXT(Homeloans!$A$1:$L$1,"d-mmm-yy")=B$1),Homeloans!$A$2:$L$2) with it changing respectively to Y2

This is the tricky part.

On Table, Cell C18 I have a data validation list setup to select a year number

On Table, E18 I have formula =SUMPRODUCT(--(RIGHT(TEXT($B$1:$Y$1,"d-mmm-yy"),1)+0=$C$18),$B$2:$Y$2)

Here is the problem.

If I set the date on Datasheet to 1/11/2011 and I select year one on C18 on the Table sheet, I get the correct sum on E18.

If I change the date on Datasheet to 1/11/2012 it flips the value in E18 to zero. This is the weird part. If I change the value in C18 to 2 for second year, then it gives me the correct sum. So I played with the formula

=SUMPRODUCT(--(RIGHT(TEXT($B$1:$Y$1,"d-mmm-yy"),1)+0=$C$18),$B$2:$Y$2) and found that if I change the highlighted part to +-1 then select year one again, it gives me the correct sum.

It sort of does what I need it to do but the problem is for every date set I will have to go in and change that value up and down the number line according to the date on Datasheet and that just isnt going to work. It need to be dynamic and pull values based on the year the values show up in.

So if a payment is entered for example on 1/11/11 as the first payment and the second payment on 1/12/11, and I want to know the totals for the first year of the loan, it should only give me that value in E18.

Any help in resolving this would be greatly appreciated. Im running out of forehead to bang against the wall!

Regards,

D

Hello,

For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com.

Please also don't forget to include the link to this comment into your email.

I'll look into your task and try to help.

Using COUNTIFS and Logical OR

For the equation below:

COUNTIFS($D2:$D$5590,1,$A$2:$A$5590,1008,$B$2:$B$5590,"LINE", $C2:$C$5590, {100,110,120,140,”WAAP”}),

How can I use the last part to count if it is 100 or 110 or 120 ….

Maybe the answer is SUMPRODUCT, but it didn't work. Could you please advise. Thank you

Hi: I'm wondering if I can use Sumproduct for two sets of arrays? here is what I want to do but it is not recognizine the "+". I am getting only the result for the first array:

=SUMPRODUCT(--('Pivot RI'!$B:$B=Summary!$A6),--('Pivot RI'!$A:$A=Summary!H$1),--('Pivot RI'!$F:$F=Summary!$B8),'Pivot RI'!$H:$H)+SUMPRODUCT(--('Pivot NV'!G:G=Summary!$A7),--('Pivot NV'!F:F=Summary!H$1),--('Pivot NV'!$F:$F=Summary!$B8),'Pivot NV'!$H:$H)

So can I not add the information from two seperate data sheets?

Thank you so much!!

Hi, I a m looking for a formula to resolve the below issue. I have unique Employee ID with duplicate Leaves. I want to highlight duplicate or overlapped leaves (start Date and End Date) under 'My Remark' column.

Appreciate your assistance.

ID# Start Date End Date My Remark

166 04-Sep-17 08-Sep-17 Duplicate Leave

166 04-Sep-17 04-Sep-17 Duplicate Leave

166 03-Sep-17 05-Sep-17 Duplicate Leave

236 01-Sep-17 01-Sep-17 Duplicate Leave

236 01-Sep-17 01-Sep-17 Duplicate Leave

243 30-Nov-17 30-Dec-17 Duplicate Leave

243 01-Dec-17 10-Dec-17 Duplicate Leave

252 01-Dec-17 10-Dec-17 No Duplicate

252 01-Nov-17 10-Nov-17 No Duplicate

Hi

How can I do the product sum of data where one of the two factors to be multiplied is the difference between two other numbers in separate columns. Is it possible to do this without creating a new column with the difference in the numbers, instead incorporating this calculation in the sumproduct formula?

Peter

Is it possible to use sumproduct to return results based on two different inputs?

A1 = "Month:" B1 = Input

A2 = Input B2 =Output

A3 = Animals

A4:L4 = Month Names

A5:L5 = Monetary Value

So Basically I enter the Month I want values from in B1

And in A2 I enter the Category, in this case Animals, and in B2 I get the value associated with the lookup from Selecting Animals Feb..

Is this possible?

Good information. Took little bit to understand but once understood it went with flying colors.

Important finding is one has to multiply the condition to both sum product columns and it works.

Thanks for posting such detailed information.

how can I use this to work over multiple sheets??

What is the correct sumproduct formula to calculate the total amount for account=1217000 and fy=18? I have tried the following, which calculates a result of 0, not 50,000.00:

=SUMPRODUCT((A6:A31="1217000")*(B6:B31="17")*(C6:C31))

Here is the worksheet.

Line 6 begins at the first row containing the numbers.

A B C

Account FY Amount

1210000 17 50,000.00

1217000 17 25,000.00

1210000 15 300,000.00

1212080 18 25,000.00

1217000 05 100,000.00

1212080 18 100,000.00

1210000 05 6,947.35

1212080 18 50,000.00

1212023 18 67,950.00

1217000 18 50,000.00

1210000 16 1,800,000.00

1210000 17 150,000.00

1210000 18 500,000.00

1212080 18 25,000.00

1212020 17 5,000.00

1210000 18 100,000.00

1210000 17 25,000.00

1212016 17 170,000.00

1212025 18 150,000.00

1210000 17 5,000.00

1217000 17 25,000.00

1212080 18 10,000.00

1217000 14 5,000,000.00

1217000 17 500,000.00

1212015 17 1,000,000.00

1212026 17 50,000.00

This has been driving me crazy.

Thanks.

Hi Paul

Today I read your problem.

Simply remove the quotes from the values

=SUMPRODUCT((A6:A31=1217000)*(B6:B31=17)*(C6:C31))

Regards

Hello,

How do write sumproduct function to calculate sum of contribution within months using the person name and type of contribution as a criteria.

E.g. assuming you have some staffs that do different type of contribution monthly. And you need a summary that gives you amount contributed by each person per month in each category of contribution

Help out

How if I want to know, how many types of fruits that sold in north??

Hi, I am trying to sum sales data in a table based on 2 criteria, where the 2nd criterion is actually a range. The sumifs formula below worked fine when the 2nd criterion was a single value rather than a range.

I need the formula to effect "sum if sales terr matches a cell AND product family matches any values in the range". I appreciate any help. Thanks

I need to sum sales data per these criteria:

1. sales territory as defined in column E in worksheet2 (in the formulas below)... I am copying the formula down a table so the territory flows e2, e3, e4, and so forth.

2. product family matches ANY one of the values in a range

=sumifs('raw data file'!SalesData,'raw data file'!Sales_Terr,$e2-sales_terr cell_in_worksheet2,'raw data file'!ProductFamily,worksheet2!(C2:C11))

=sumproduct(('raw data file'!Sales_Terr=$e2),*('raw data file'!ProductFamily={worksheet2!(C2)}+('raw data file'!ProductFamily={worksheet2!(C3)} .. etc.. 'raw data file'!SalesData))

Hello!

I'm struggling with a weighted average using same column. Any advice is appreciated.

Example table:

Section Indicator Type Data

Sec1 IndA x 100

Sec2 IndB x 20

Sec n Ind m -- number

Weighted average using criteria Section, Indicator, and Type.

Basically, for Type X to make Indicator B weighted average using Indicator A.

As a note, the table is really big and there are may indicators and types. however, the consolidation is to be per type. Section is an additional filter as one indicator (with different unit of measure) may appear in other section. Using this filter will assure consistency.

Thank you in advance for your time and help!

Hi,

Plz provide unique count qty formulas.

Great article!

I've struggle to apply the sum-product formula to reference Table arrays. It always returns a #Value! Error. The same formula woill work perfectly on a range of the same data. Can anyone offer advice?

Great article!

I've struggled to apply the sum-product formula to reference Table arrays. It always returns a #Value! error. The same formula will work perfectly on a range of the same data. Can anyone offer advice?

Hi,

I have always used the multiplier to calculate SUM for multiple criteria (column and rows) & was trying the same using the double unary operator but it keeps returning a #value error

The formula is:

=SUMPRODUCT(--($H$32:$H$34="Mark"),--($I$31:$L$31="Q1"),I32:L34)

Could you please help what is incorrect about this formula - trying to understand the logic!

Thanks

Hi

I am want to understand the logic in this formula from a cashbook workbook. Please help.

=SUMPRODUCT((BalDate<=($A5+(ROW($A5)/86400)))*(Type="D")*(BankCode=$H5)*(Amount))-SUMPRODUCT((BalDate<=($A5+(ROW($A5)/86400)))*(Type="W")*(BankCode=$H5)*(Amount))

Thanks

Hello, what functions should I use to count the number of items that the North sell? (My table contains around 1000 rows, it scares me somehow, please help). Thank you.

A B C

1 Sl No Name No of Items

2 1 North 20

3 2 Divakara 25

4 3 Suhasini 100

5 4 North 88

6 5 Soumya 101

The forumula to count the number of items that the North sell is:

=sumif(B2:B6,"North", C2:C6). It will return answer 108.

Name startingD END DT AMT

A 01-01-08 05-01-08 4

b 02-01-08 06-01-08 4

Output be with only someproduct functions plz,

date b a

01-01-08 4

02-01-08 4 4

03-01-08 4 4

04-01-08 4 4

05-01-08 4 4

06-01-08 4

#Mark Turkenberg:[7/1/2018, 1:00AM]

> Your formula is:

=SUMPRODUCT(--($H$32:$H$34="Mark"),--($I$31:$L$31="Q1"),I32:L34)

> Is your data in rows 32:34, or 31:34?

> For SUMPRODUCT to work, the data has to be in the same rows and columns. "The array arguments must have the same dimensions." I think your data is either in the range I32:L34 or the range I31:L34.

> I'm going to guess that col. H contains names, col. I contains the quarters (e.g. "Q1"), and col. L contains an amount.

> Your formula's 2nd term, --($I$31:$L$31="Q1"), seems to require that all four cells I31:L31 contain the value "Q1". I don't think that's what you mean. And it's not clear whether row 31 is in your table I32:L34.

> I think you want the 3rd term, "I32:L34", to include the values in one column only (I, J, K, or L), not all the values in all those columns.

> I'm going to assume that your data is in the range I32:L34, and the amounts are in col. L.

> If my guesses are correct, then this ought to work:

=SUMPRODUCT(--($H$32:$H$34="Mark"),--($I$32:$I$34="Q1"),$L32:$L34)

> Note that, in each of the formula's terms, the range specified has the same rows and one column only; otherwise, you haven't defined a valid range of data.

> Please let me know if that helps, OK?

#Sharon [7/17/2018, 4:02 PM]

> Your formula is:

=SUMPRODUCT((BalDate<=($A5+(ROW($A5)/86400)))*(Type="D")*(BankCode=$H5)*(Amount))-SUMPRODUCT((BalDate The formula has two identical subformulas. the first one sums the Type="D" items, where BankCode = $H5 and the Balance Dates that are greater than or equal to the term, "($A5+(ROW($A5)/86400)" the second one does the same thing, but with the Type="W" items. Then the answer equals the first summed amounts minus the second summed amounts.

> I don't understand the working of the term, "($A5+(ROW($A5)/86400)", other than the "86400" factor will convert seconds to days...

> What are some of typical values in the cells of col. A? What is the cell format?

> Is this formula in your firm's cashbook workbook producing accurate results? I would guess that it isn't! Hopefully you will provide us some more insights. Thanks, #Sharon.

#Paul [4/18/2018]:

Your formula:

=SUMPRODUCT((A6:A31="1217000")*(B6:B31="17")*(C6:C31))

> Excel will treat your second factor, "(B6:B31="17")", as a string value, "17", if you enclose the value in quotes.

> Either you have to (1) reformat the cells in col. B so that they are "Text", or (2) remove the quotes from the "17" in the formula.

=SUMPRODUCT((A6:A31="1217000")*(B6:B31=17)*(C6:C31))

> Does it work now?

Hi

I have a challenge for you guys out there :)

I have the following scenario:

Excel Sheet 1 :

A1 : Apples

A2 : Oranges

A3 : Watermelon

A4 : Lemon

...etc...

A30 : Strawberry

I would like to use ALL the above as criteria on which to base my SUMIF, i.e. if A3 : Watermelon is removed from the list, my SUM should not return @watermelon' values but should return all the rest ....

Any ideas ?

Thanks

Brian

can anybody helps me my problem. I need to get the total value of column C by searching

Other Sheet Column A = Column B total value Column C.

MasterSheet

Column A Column B Column C

Galley - Main Galley Pasta/Hot App/Sour/Entremetier 127.96

Fish & Seafood 33.21

Basic Prep Hot & Cold 737.50

Galley - World Cafe Menu Requisition 193.04

Meat 674.66

Galley - Chef's Table Menu & Basic Prep 994.88

Meat 1,444.70

OtherSheet

Column A Column B Column C

Galley - Main Galley Basic Prep Hot & Cold 283.63

Galley - Main Galley Basic Prep Hot & Cold 244.12

Galley - Main Galley Pasta/Hot App/Soup/Entremetier 127.96

Galley - Main Galley Basic Prep Hot & Cold 209.74

Galley - Main Galley Fish & Seafood 33.21

Galley - Manfredi's Menu & Basic Prep 232.84

Galley - World Cafe Menu Requisition 193.04

Galley - World Cafe Meat 674.66

Galley - Chef's Table Menu & Basic Prep 127.30

Galley - Chef's Table Menu & Basic Prep 333.21

Galley - Chef's Table Meat 1,444.70

Galley - Chef's Table Menu & Basic Prep 534.36

i know only for one column search see below sample only

=SUMPRODUCT(('OtherSheet'!$A$1:$A$10='MasterSheet'!A1)*('OtherSheet'!$C$1:$C$10))

thank you in advance..

Hello Team, I am having the below data and want to get the weighted price at the last column below and got stuck to go about. please help!

Region1 Region2 Region3 Weighted Total

mass Price mass Price mass Price mass weighted Price

300 15 200 10 250 20

=SUMPRODUCT(--('[Source Data..xlsx]Pivot Drops to Field'!$A:$A=A7),--('[Source Data..xlsx]Pivot Drops to Field'!$B:$B=Z1),--('[Source Data..xlsx]Pivot Drops to Field'!$B:$B=AA1),('[Source Data..xlsx]Pivot Drops to Field'!$C:$C))

What is wrong with this formula?

Hello Team, I've below table of data, I want to get total count of places which contains (3 letters and 3 digits) in its name without calculating the duplicated values (In below example I have 7 unique values).

in other cell, I would like to have same above condition, but with 2 additional conditions that the place which fall under code 5 of column B, and was recorded between 05:00 till 17:00 in column C

thank you for ur kind cooperation.

A B C

1 Place Code Time

2 LHR123 5 0:40

3 LHR123 6 18:40

4 NYC 3 7:43

5 ATL586 2 9:53

6 DFW 5 13:25

7 ORD563 5 17:11

8 NIC123 4 8:40

9

10

11 ATL586 3 10:10

12 DFW564 1 1:01

I’m trying to create a SUMPRODUCT for 3 criteria and add multiple columns. Similar to Example 3 above, but add a column to the right of Item, call it Name, and the data is for a year across columns to the right. This is the formula I'm trying...

=SUMPRODUCT((($D$1:$O$1>=$Q$1)*($D$1:$O$1=$Q$1)*($D$1:$O$1<=$R$1))*($A$2:$A$49=$S$1)*($C$2:$C$49=$U1)*$D$2:$O$49)

but when I try to add in the 3rd criteria the formula returns #VALUE :( help

Why is half of my message missing.

I’m trying to create a SUMPRODUCT for 3 criteria and add multiple columns. Similar to Example 3 above, but add a column to the right of Item, call it Name, and the data is for a year across columns to the right. This is the formula I'm trying...

'=SUMPRODUCT((($D$1:$O$1>=$Q$1)*($D$1:$O$1=$Q$1)*($D$1:$O$1<=$R$1))*($A$2:$A$49=$S$1)*($C$2:$C$49=$U1)*$D$2:$O$49)

This is the formula I'm trying...

'=SUMPRODUCT((($D$1:$O$1>=$Q$1)*($D$1:$O$1<=$R$1))*($A$2:$A$49=$S$1)*($B$2:$B$49=$T$1)*($C$2:$C$49,$U1)*$D$2:$O$49)

Cont.

D1:O1 is the months of the year / Q1=1/1/19 / R1=2/1/19 / A2:A49 is the Region / S1=North / B2:B49 are the Items / T1=Apples

C2:C49 is the Name / U1=Bob / D2:O49 is the data.

D1:O1 is the months of the year

Q1=1/1/19

R1=2/1/19

A2:A49 is the Region

S1=North

B2:B49 are the Items

T1=Apples

C2:C49 is the Name

U1=Bob

D2:O49 is the data.

This formula works for 2 criteria, Region and Name...

'=SUMPRODUCT((($D$1:$O$1>=$Q$1)*($D$1:$O$1<=$R$1))*($A$2:$A$49=$S$1)*($C$2:$C$49=$U1)*$D$2:$O$49)

Hello Eric,

Our blog engine sometimes mangles comments, sorry for this.

Your formula won't work, firstly, because D1:O1 (text values) cannot be compared to Q1 and R1 (dates); and secondly because the ranges ($D$1:$O$1, $A$2:$A$49, etc.) are of different size.

If, in your summary table (Q1 or R1), you enter the target month exactly as it is written in $D$1:$O$1, then the task can be accomplished with an array formula that you can find in this sample sheet.

Hi Svetlana.

I am trying to create Sumproduct formula to calculate total sum of deviations between OB and CC if deviation is >0 (I need to calculate 9). With this formula '=SUMPRODUCT(B2:I2-B3) I receive 1 that is total of all deviations. How to add a criteria >0 to the formula?

wk1 wk2 wk3 wk4

OB 17 29 25 30

CC 25 25 25 25

0 4 0 5

Thank you in advance!

Thanks for teaching me the sumproduct formula in details

I'd like assistance on something that seems easy. I want A1*B1 + A1*C1 + A1*D1

I tried SUMPRODUCT(B1:D1, {A1,A1,A1}) but that doesn't work.

Can you help? Thank you in advance!

I appreciate you are busy and may not be able to Tokyo but I think it’s still worth and ask, fingers crossed. I am using the sumproduct formula below but can’t get it to work dynamically so I don’t have to edit it for different table headers of which Cit13_Hits is just one of many. In addition the conditional value 5 that it is summing for is also variable and can be from 1-9.

When I use cell references for these parts of the formula then the formula fails. Is here any way to carry out this so I can simply copy the formula without having to edit it manually for the changing variable name and values?

=(SUMPRODUCT(--(LEFT(Results[CIT13_Hits],1)="5"),--(INDEX(Results,0,MATCH($H$4,Results[#Headers],0))="CY")))

Many thanks

Gary

Hello!

Thank you for this post. It is very well done.

Any chance you know of a way to sumproduct only cells that are visible and exclude blank values from the calculation?

Kind Regards,

Thank you very much...

Hi, I've searched the internet for a way to extract numbers only from a text string using a formula and found this

=SUMPRODUCT(MID(0&A5, LARGE(INDEX(ISNUMBER(--MID(A5, ROW(INDIRECT("1:"&LEN(A5))), 1)) * ROW(INDIRECT("1:"&LEN(A5))), 0), ROW(INDIRECT("1:"&LEN(A5))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A5)))/10)

I would like an explanation how this works.

Many thanks in advance.

I need assistance on extracting just the large index number of this column that contains. Not the text just the larger index.

CR 5006193154

Hello Sir,

Good Evening

My Name is Niru Kumar. My problem is very simple I have Sheet1 raw Data sheet 2Class1,sheets3 Class2,sheets4 Class3

are three sheets, I tell your if sheet1 raw data put class1 go to sheets2Class1 sheet1 raw data put Class2 go to sheets3 Class2.this is my problem

I am trying to use SUMPRODUCT to multiply the Hours * Rate of a data source for Timekeeper = X and for hours recorded in month =1.

Column B = Date

Column C = Hours

Column K = Rate

Column N = Timekeeper

Every time I do it I get zero or #value error and can't figure it out what I am doing wrong.

I have used the Sumifs formula to calculate the total Hours for the timekeeper X for Month 1 and that works great =SUMIFS(activities!$C:$C,activities!$N:$N,Monthly!C$5,activities!$B:$B,">="&Monthly!$A6,activities!$B:$B,"<="&EOMONTH($A6,0),activities!$L:$L,""&"")

Thank you for your assistance.

Thank you for this tip.

Need to create dashboard for time log, we have data as date, task, activity and time spent.

Activities are with respective to Tasks but there was no condition given so people have mixed the task and activities. Need to prepare a dashboard to show the miss match data and correct data with time spent.

For example : Below date is miss match

Date Task Activity hours

12-Oct-19 Out of office Idle 8

13-Oct-19 Running Idle Leave/off 8

Below data is correct data

Date Task Activity hours

07-Oct-19 Out of office Leave/off 8

08-Oct-19 Running Idle Idle 8

Task and activities with respective time spent dash board needed. Please help. I'm missing the logic to write the formula for dashboard.

I have problem with selecting right column when creating sumproduct formula.. How should define column depending on day number? For example when it is 6th may it must look 6th column which is marked as 6 in first row. Next day should look into 7th column etc....

I have 3 columns. One is on the first tab and the other 2 are on the second tab. I want the column on the first tab to find any matching values in the column in the second tab. If there is a match/multiple matches, I want it to grab the values in the 3 column on the second tab and return the sum. Hope this makes sense and there is someone that can assist! Thank you,

Nice work. Very helpful