Excel SUMPRODUCT is a remarkably versatile function with many uses. The aim of this tutorial is to reveal the full power of SUMPRODUCT and teach you how to compare arrays in a clever and elegant way, conditionally sum or count cells with multiple criteria, get a weighted average, and more. Continue reading
Comments on: Excel SUMPRODUCT function with formula examples
by Svetlana Cheusheva, updated on
Comments page 7. Total comments: 247
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.
A big "Thank you" for helping me out in understanding Sumproduct function
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?
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
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. ^_^
kindly tell me how to get the total of two items through Sumproduct formula...
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
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 Home tab, in the Styles group, click Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the next pop-up window select Unique, choose the colour and press OK.
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.
Used =sumproduct(1/countif(array1,array2)) to count unique values in a column with duplicates but once table is filtered the answer doesnt change.
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! :)
geez 1 more time
=SUMPRODUCT((F9{1,3,9}))
=SUMPRODUCT
((F9{1,3,9}))
oh well, delete something. my post got chopped.
=SUMPRODUCT((F9{1,3,9}))
hi, i don't think i saw an example for multiple levels. i think this will work. thanks.
=SUMPRODUCT((F9{1,3,9}))
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.
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 Concern,
Thanks a lot for made a good opportunity to know about excel. Very helpful article.
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.
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))
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'd like to sumproduct the two column,that the value are negative.
please help me.
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.
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.
thnx
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
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,
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
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.
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
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