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 3. Total comments: 245
how come ther is no goto command in spreadsheets for exaple
cell a1 input a1
cell a2 if a1 = "home" the goto d1
cell c1 'go
cell c1 c2 = a1
answer
go home
how come we do not have that formulas in spreadshet
Hi!
For conditional calculations, you can use the IF function.
Or use VBA language.
Hello,
Type Sub Code Amount Product
Cost 100 20 A
Sale 100 30 A
Cost 200 40 A
Sale 200 50 A
Sale 300 60 B
I am trying to divide Cost/Sale to find the Percentage for a product group but by each sub code. For example Product A, Sub Code 100: 20/30 + Product A, Sub Code 200: 40/50 and then get the total percentage. I am able to do the divide for the total amount by Product, but I want the divide function to run for each sub code separately and then add it to get to the total Percentage
Hello!
The location of your data makes it very difficult to do the calculations you need.
You can get the corresponding Sale values in column E using the INDEX+MATCH formula:
=INDEX($C$2:$C$6,MATCH(D2&B2&"Sale",$D$2:$D$6&$B$2:$B$6&$A$2:$A$6,0))
After that, you can divide the desired values from column C by column E, and also calculate percentages.
Hi, I think this one might be easy for you, on my side I have been trying for some times now. I would like to create a SUM formula that I can drag down over hundreds of rows that could SUM some figures from the same column depending of the title of this collumn and what is written in the rows. I am not so sure if sumproduct if the best, I would like not to use Sumifs as it would be unreadable and multiple lines of formulas.
Let's imagine;
Collumn A : Shop 1, Shop 2, Shop 3
Collumn B : pear, apple, cherry, apple, cherry, orange, pear
Column C : small, medium, big, small, medium, big, huge.
collumn D : 1st year
Collumn E : 2nd year
Collumn F : 3rd year
collumn G : 4th year
For the rows Collumn A to C, it would be as described: shop, product, size (over 1xxx rows)
and Collumn D to G we will have the quantities sold. (historical TAB that can't be upgraded easily...)
Then, what I would like is in another TAB and in 1formula, to sum the figures depending of the year the product, the size and the shop.
Again, I know I could use sumifs (I think ><), but I would like to avoid that. maybe sumproduct is not the best, wish you could help me on this one.
Thank you very very much for your help and time.
Remy
Hello!
If you want to sum values based on a column heading, then I recommend using a pivot table. There, on a separate sheet, you can choose which column you want to summarize and by what criteria.
Hi,
Thank you for your answer, I will try that.
Hello,
I am using the following formula =SUMPRODUCT(--ISNUMBER(SEARCH(Sheet2!$D$82:$D$5523,C3)))>0
I have column C in which I have one or more email addresses delimited with ";". With my formula I am able to check if one of these emails in each cell is used in another Sheet2!$D$82:$D$5523. This seems working well, I am getting true if email exists.
Now I am blocked, I would like to get the value of another column in Sheet2 that matches my search.
Any Idea?
Thank you,
Hello!
Sorry, it's not quite clear what you are trying to achieve. Give an example of the source data and the expected result.
It’ll help me understand it better and find a solution for you.
I want to count number employee who are at 25th,50th,75th percentile of a salary range which i have bifurcated Role wise and experience band wise. Below is the formula which is returning zero as answer kindly help.
1)G:G is Deaprtment Code
2)K:K is Experience Band Code
3) M:M is Designation Code
4) AN:AN is Salary
=SUMPRODUCT(('Active Internal Salary'!$G$2:$G$2192="SOM")--('Active Internal Salary'!$K$2:$K$2192=$B15)--('Active Internal Salary'!$M$2:$M$2192=$B$1),'Active Internal Salary'!$AN$2:$AN$2192<PERCENTILE('Active Internal Salary'!$AN$2:$AN$2192,0.25))
Hiya
I am trying to work with three separate columns of data, ie.
A B C
Red 31 5
Blue 29 10
Yellow 50 20
Green 29 15
Red 31 50
What I am trying to accomplish is I want to be able to work out for each variable in Column A whether it is equal to 31 in column B and if it is then count those in column C on the same row but exclude everything else?
So as above Red = 55, Blue = 0, Yellow = 0, Green = 0.
I have approximately 60000 lines of data.
Your help is very much appreciated!
Hello!
To find the sum of values across multiple criteria, use the SUMIFS function as described in this tutorial.
This should solve your task.
Great explanation.
I have a tough one for you. I manage our sports team and create teams by handicap.
My table consists of 'team number', 'date', Lname, wins, 'total games' and 'team number'. The table rows are added by date at the end of the table, IE latest date is last.
First: I want the handicaps (Wins/Total Games) for the most current (variable=10) rows by Last Name.
Second: Teams, with Last name, sorted the highest team haandicap.
Working on the first formula is have:
=SUMPRODUCT(--(tblHistory[Total Wins]>0),--(tblHistory[Lname]="lane"),tblHistory[Total Wins]) / (SUMPRODUCT(--(tblHistory[Total Games]>0),--(tblHistory[Lname]="lane"),tblHistory[Total Games]))
Which gives me the total for the table...I only want the last X games of handicap.
Can you help?
Hello!
It is very difficult to understand a formula that contains unique references to your workbook worksheets. Please have a look at this article — Excel LARGE function to get N-th highest value.
Sorry for not being explicit enough.
Here is a pic of what my table look like:
Table headers are:
First Name(fname), Last Name(Lname, Team number (1-6), play date, wins, total games.
I would like to get the last 10 play dates of LName both wins and total games.
No everyone plays so the last play date is mostly different.
The goal is to find the last 10 games played and determine their handicap by summing wins divided by summing total-games.
As from above I can get total-total of played and games for a player buy the range is too large.
Last 10 days will provide a good average.
Hello!
With the FILTER function, you can select rows with the 10 last play dates. Then, with this data, you can do the calculations you want.
You can find the examples and instructions here: How to limit the number of rows returned by FILTER function.
This should solve your task.
Hello,
I appreciate the help to use SUMPRODUCT() to calculate the weighted average (Weight & Cost) but only if the value (Center) is found within a designated range of cells.
In the example below, I would like to obtain the WAvg for Set 1 and Set 2. The actual data set is thousand of entries and hundreds of centers, resulting in numerous Sets. Single entry such as if ="apple" OR "lemon" as shown in the lesson will not be feasible to process the large volume. Is it possible for the conditional statement to be a cell range instead of individual values?
Thank you for the help!
Set 1: Centers 949 and 1200
Set 2: Centers 5300 and 3687
Weight Cost Center
5 12380 949
2 90375 1200
3 38306 1200
4 49073 949
5 41498 5300
6 35196 3687
6 28948 949
5 83636 5300
1 21753 5300
1 53236 1200
Hello!
Add conditions to the SUMPRODUCT formula:
=SUMPRODUCT(A2:A11,B2:B11,--(C2:C11>=949),--(C2:C11<=1200))/SUMPRODUCT(A2:A11,--(C2:C11>=949),--(C2:C11<=1200))
I hope my advice will help you solve your task.
Can SUMPRODUCT be used in the case where column A1:A100 has an hourly rate and Column B1:B100 has # hours and I want to multiply the rate by #hours for each 3rd row in the array? For instance, a quicker way to write (A1*B1)+(A4*B4)+(A7*B7)... The scenario is that I have multiple people on the project and each bills a different rate. Each week I need to summarize the total burn rate.
Hello!
If your data starts on line 2, then in order to find the sum of the products in every third line, you can use the formula:
=SUMPRODUCT(A2:A10,B2:B10,--(MOD(ROW(A2:A10)+1,3)=0))
I hope I answered your question. If something is still unclear, please feel free to ask.
I am hoping to sumproduct two arrays, where each array is defined in part by one criteria that differs between the two arrays (column A = series). The columns are arranged so that A = series, B = aggregation tag, C = country, and D = value. I need sumproduct (Series XR*Series GDPL) if B = a certain tag (see examples at bottom). If I use the formula:
=sumproduct(--(A:A="XR"),--(B:B="ASEA"),D:D)*sumproduct(--(A:A="GDPL"),--(B:B="ASEA"),D:D))
I get sum(XR) * sum(GDPL)
But what I need is for the two sumproducts to return arrays, then sumproduct the arrays, like
ID XR * ID GDPL + MY XR * MY GDPL...etc.
Thank you in advance!
A B C D (value)
XR ASEA ID
XR ASEA MY
XR ASEA PH
XR ASEA TH
XR IN
XR IT
GDPL ASEA ID
GDPL ASEA MY
GDPL ASEA PH
GDPL ASEA TH
IN
IT
Hi!
I don't really understand what you want to do. But if you need to get an array of values, then instead of
sumproduct(–(A:A=”XR”),–(B:B=”ASEA”),D:D)
use
(A:A=”XR”)*(B:B=”ASEA”)*D:D
I do not recommend using an entire column reference (eg D: D). This slows down the calculation very much.
Please help me with my sum product formula with multiple criteria:
=SUMPRODUCT(('Sheet1 (3)'!$H$3:$H$3194=Sheet1!D6)*('Sheet1 (3)'!$I$2:$T$2=Sheet1!C6)*('Sheet1 (3)'!$C$3:$C$3194=Sheet1!B6)*('Sheet1 (3)'!$E$3:$E$3194=Sheet1!F$5)*('Sheet1 (3)'!$A$3:$A$3194=Sheet1!$F$4)*'Sheet1 (3)'!$I$3:$T$3194)
I am having a 0 result on my formula. Do I have too many criteria? Please help to correct my formula.
1st criteria- column H - interval 1-24
2nd criteria - (horizontal) I - T - interval 1-12
3rd criteria - column C - Date
4th criteria - column E - Place of Origin
5th criteria - customer type
value to sum up - I - T
Thank you.
Hello!
You cannot use both vertical and horizontal criteria at the same time. I can’t give you a bit of better advice, because I don’t see your data.
Hi!
Below is my raw data file:
Col/Row: A1/B1/C1/D1/E1/F1/G1/H1/I1/J1
Customer/Date/Place of Origin/Interval (1-24)/00:05/00:10/00:15/00:20/00:25/00:30 - every 5mins
AA/10-14-2021/Arayat/1/00.01/00.11/00.05/00.24/00.58/00.45
AA/10-14-2021/Arayat/2/00.25/00.15/00.85/00.14/00.75/00.48
AA/10-14-2021/Arayat/3/00.21/00.54/00.15/00.14/00.65/00.87
AA/10-15-2021/Kuliat/1/00.22/00.54/00.47/00.54/00.98/00.65
AA/10-15-2021/Kuliat/2/00.01/00.11/00.05/00.24/00.58/00.45
AA/10-15-2021/Kuliat/3/00.01/00.11/00.05/00.24/00.58/00.45
AA/10-16-2021/Angeles/1/00.01/00.11/00.05/00.24/00.58/00.45
AA/10-16-2021/Angeles/2/00.25/00.15/00.85/00.14/00.75/00.48
AA/10-16-2021/Angeles/3/00.21/00.54/00.15/00.14/00.65/00.87
AB/10-14-2021/Arayat/1/00.21/00.54/00.15/00.14/00.65/00.87
AB/10-14-2021/Arayat/2/00.01/00.11/00.05/00.24/00.58/00.45
AB/10-15-2021/Arayat/3/00.01/00.11/00.05/00.24/00.58/00.45
AB/10-15-2021/Kuliat/1/00.01/00.11/00.05/00.24/00.58/00.45
AB/10-16-2021/Kuliat/2/00.22/00.54/00.47/00.54/00.98/00.65
AB/10-16-2021/Kuliat/3/00.22/00.54/00.47/00.54/00.98/00.65
Below is my working file:
Horizontal Criteria: AA &AB - Col/Row: D1,E1
Horizontal Criteria: Arayat, Kuliat,Angeles - D2,E2,F2,G2,H2,I2
Vertical Criteria: Date, Minutes,Interval - A2,B2,C2
Formula: D3
AA/ AB
Date/Minutes/Interval/Arayat/Kuliat/Angeles/Arayat/Kuliat/Angeles
10-14-2021/00:05/1/FORMULA
10-14-2021/00:10/2/FORMULA
10-14-2021/00:15/3/FORMULA
10-15-2021/00:05/1/FORMULA
10-15-2021/00:10/2/FORMULA
10-15-2021/00:15/3/FORMULA
I hope these help to view my data. I need the total value for every origin from those multiple criteria above, using the formula of sumproduct, the total does not much when I check the raw data file vs. the working file, I get a bigger result. Please help. Thank you.
Hello!
Unfortunately, I don't understand what you want to calculate.
I cannot guess what it means - "Horizontal Criteria: Arayat, Kuliat,Angeles – D2,E2,F2,G2,H2,I2".
What result do you want to get?
Hi!
I want to get the total values from the place of origin: Arayat, Kuliat and Angeles given the multiple criteria: Customer type, Date, Interval (1-24) and Minutes (every 5mins.)
Thank you.
Hello!
If I understand correctly, you can use the SUMIFS function to calculate the sum based on the multiple criteria.
Hello!
Problem solved! Thank you so much for your assistance.
very nice
Good day,
To exclude text values in sumproduct, since the formula is not filtering it by itself,
use an if formula as below:
=SUMPRODUCT(--ISNUMBER(G2:G205)*IF(ISNUMBER(I2:I205),I2:I205)
i have Y axis Account code which can be duplicate, and X aixs Month.
How can I use sumprouct to show each month's spending by account?
thank you
Hello -
Im tried to adjust some existing sales forecasts with specific logic using weighted averages. If I have the following:
Product Month LY Sales Planned Sales Actual Sales Adjusted Plan Sales
A B C D E F
Prod 1 Jan 100 120 130 0
Prod 1 Feb 120 120 140 0
Prod 1 March 120 135 125 0
I have multiple products with an existing forecast established but I am trying to retrend the future sales plan based on users preference (option 1: trend on weighted average of 50% LY/50% Last 3 month avg) or (option 2: trend sales based on weighted average 75% current plan/25% Last 3 months actuals) and so on. Insure if I use sum product formula or how to approach.
Hi, I want to make an excel sheet with daily wages and extra hours pay summation for my employees, individually.
Hello!
Unfortunately, without seeing your data it is difficult to give you any advice. For me to be able to help you better, please describe your task in more detail. Please provide me with an example of the source data and the expected result.
Hello,
Can you please help me resolve if I want to apply sumproduct in all the rows then how I need to do this? Currently when I am doing this then its changing the formula every time I press enter or ctrl+enter or ctrl+shift+enter.
Hello!
Sorry, I do not fully understand the task. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred.
It’ll help me understand it better and find a solution for you.
Hello excel gurus,
Based on table 1, is there a way to calculate the number of tasks a given resource (who) is assigned and working for each calendar day (table 2).
I'm enclosing the link to the image of the two tables - https://postimg.cc/YGRj9TBn
I've given a try using sumproduct or countifs but I haven't found the way to get the desirable results.
I will appreciate any insight about it.
Thanks!
SECURITY PREV_CL_PR OPEN_PRICE HIGH_PRICE LOW_PRICE
3M INDIA LIMITED 18399.7 18401.95 18426 18190.05
63 MOONS TECHNOLOGIES LTD 68.65 68.75 69.4 68.1
3M INDIA LIMITED 18307.15 18450 18500 18151.05
63 MOONS TECHNOLOGIES LTD 68.35 68.65 71.75 67.15
3M INDIA LIMITED 19353.15 19400 19612 19100
63 MOONS TECHNOLOGIES LTD 78 78 79.9 76.25
3M INDIA LIMITED 18747.5 18750 18949.9 18130.55
3P LAND HOLDINGS LIMITED 6.65 6.35 6.35 6.35
63 MOONS TECHNOLOGIES LTD 75.75 74.2 78 72.25
got high price by max function but not able find the low price by min function
=SUMPRODUCT(MAX((D2:D12000) * (A2:A12000=A2) ))
but same in min function gives 0
=SUMPRODUCT(MIN((D2:D12000) * (A2:A12000=A2) ))
Hello!
I believe the following formula will help you solve your task:
=MINIFS(D2:D12000,A2:A12000,A2)
You can learn more about MINIFS in Excel on our blog.
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?