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 4. Total comments: 247
Was looking at a database set-up by someone and long gone; came across this formula:
=SUMPRODUCT(--(DELIVERY[DATE]<$C$3),DELIVERY[105MM BAG])-SUMPRODUCT(--(DELIVERY[DATE]<$C$1),DELIVERY[105MM BAG])
Please help me understand what this formula means in words as seen in the examples above.
Note: "C3" is a date - start of a new month
"C1" is a date - start of the previous month or just ended
"Delivery" - The worksheet name
"105MM BAG" - Title of a column in the Delivery worksheet and product being counted
Hello!
If I understand your task correctly, the formula calculates the amount for the previous month
Dear How can i use sumproduct formula for two, three column and sum function for other columns and finally multiply result with one cell. thanks
good explanation and we are learning this excel in this page
Thanks for you help
IF(SUMPRODUCT(--(F$26>=Inputs!$B$2:$B$22),--(F$260,1,"")
I want particular column value instated of 1 in this formula
IF(SUMPRODUCT(--(F$26>=Inputs!$B$2:$B$8343),--(F$260,1,"")
I want particular column value instated of 1 in this formula
Hi,
I'm figuring out a formula to count how many times the value is repeated based on a reference. I have summed the total quantity from the duplicate - Product name and barcode (Column E)
Ex: A = Name of the Store
B = Item Name
C= Barcode
D = Quantity
E = Sum of Duplicate - Using product name and barcode
F = ? First I want to check how many times the store name is repeated and then identify the duplicates within that store and later count the duplicates against the barcode or product name. So if same product it is identified within that store name, I can sort and delete it. Or if you can sum the duplicate within the store also good for me.
Hello,
I am after some help please?
I have a worksheet with 4 columns (Reason, StartTime, EndTime & Total Minutes), the Total Minutes are calculated as EndTime-StartTime to give a value in HH:mm:ss example data with columns seperated by ~ below:
Fixed Break~24/12/2019 12:15:03~27/12/2019 10:11:59~69:56:56
Fixed Break~25/12/2019 12:00:00~25/12/2019 13:00:00~01:00:00
Weather~25/12/2019 13:30:00~25/12/2019 17:30:00~04:00:00
What I need to do is to get a sum of the Total Minutes where Reason = "Fixed Break" & Total Minutes > 180 (3 hours).
I can get the total minutes of the records over 3 hours with the following but this is including the Weather record (where Total Minutes is a merge of columns N to P):
=SUMPRODUCT((Quay7!$N$113:$P$139-TIME(0,0,0))*(Quay7!$N$113:$P$139>TIME(0,180,0)))
I can also get the total minutes for Reason = "Fixed Breaks" with the following (but this includes the 1 hour record):
=SUMIF(Quay7!$A$113:$D$139,"Fixed Break",Quay7!$N$113:$P$139)
However when I try and combine both of these functions I either get a #VALUE error or a blank cell.
Your help will be very much appreciated.
Many Thanks & Kind Regards
Chris Neeves
Nice work. Very helpful
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,
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....
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.
Thank you for this tip.
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.
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 need assistance on extracting just the large index number of this column that contains. Not the text just the larger index.
CR 5006193154
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.
Thank you very much...
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,
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
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!
Thanks for teaching me the sumproduct formula in details
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!
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.
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.
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.
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)
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)
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
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
=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 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
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..
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
#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?
#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.
#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?
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
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.
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
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
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?
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?
Hi,
Plz provide unique count qty formulas.
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, 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))
How if I want to know, how many types of fruits that sold in north??
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
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
how can I use this to work over multiple sheets??