Comments on: Excel SUMPRODUCT function with formula examples

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 page 2. Total comments: 245

  1. =SUMPRODUCT(SUMIF(INDIRECT("'"&'1:31'!&"'!C:C"),"A&P CHECK,INDIRECT("'"&'1:31'!&"'!D:D")))
    hi, is this formula correct?
    I want to sum Column D in sheets 1 to 31 if the column C contains text "A&P CHECK"

  2. How would you manage the syntax when pulling data from other workbooks with multiple criteria: sumproduct(--(different workbook sheet1 range = A2)*(different workbook sheet2 range=20),(different workbook range to bring in))

    Thank you!

  3. Have a sheet with 2500 rows and 42 columns. Utilizes Sort & Filter for the header rows (row3). A summary of what review code meaning is the top row/colums for each review code. I would like that count to dynamically change as the filters are changed.

    I've tried the functions below but have issues with each.
    SUMPRODUCT(COUNTIF(B3:B7,"*04a*"))*(SUBTOTAL(3,OFFSET(B4,ROW(B3:B7)-MIN(ROW(B3:B7)),0))) but gives #SPILL errors when used in columns that has data below it.

    SUMPRODUCT((B4:B7="04a")*(SUBTOTAL(3,OFFSET(B4,ROW(B4:B7)-MIN(ROW(B4:B7)),0)))) works but only if I give it exact match criteria. Any use of wildcards results in 0 record count being returned. In the sample data provide, row5 and row6 contain 04a so the result should be 2 which matches the Filter selection.

    Is there a way to have the header summary dynamically update when the filter is changed. I frequently filter on items that may be embedded with the other review codes so need a contains text solution.

    c1 c2 c3 etc..
    r1 01a count based on applied filter 04a count based on applied filter 04f count based on applied filter etc…
    r2
    r3 review code product State
    r4 01a Apple FL
    r5 04a Orange FL
    r6 01a 04a 10a 12a Apple AZ
    r7 04f 10a Cherries CA

    1. Hello!
      To search for a text string in a cell, instead of (B4:B7="*04a*"), use the expression

      ISNUMBER(SEARCH("04a",B4:B7))

      SUMPRODUCT((--ISNUMBER(SEARCH("04a",B4:B7))) * (SUBTOTAL(3,OFFSET(B4,ROW(B4:B7)-MIN(ROW(B4:B7)),0))))

      This should solve your task.

      1. Works like a champ!
        Looks so simple now that I see the solution

        Thank you again

  4. Hello to all

    I am trying to return the sum of the value from a specific column based on multiple criteria in a row and/ or column.
    As an ex-example:

    Database
    Column A Column B Column C Column D
    Row month date Type Project name Sales
    Row 1 01/31/2022 Revenue1 Kansas 100
    Row 2 03/22/2023 Revenue2 Texas 26
    Row 3 01/23/2023 Revenue1 Kansas 150
    Row 4 01/31/2023 Revenue1 Kansas 10

    I need to create the sumproduct condition that will return the value 260, which is the sum Row 1, Row 3, and Row 4 based on the below criteria:
    Q1-2023 (which will collect 01/23/2023 and 01/31/2023), Revenue1, Kansas as the project name.

    I tried with the SUMIFS but the formula is static through a specific column.

    Thank you in advance for your help.

    1. Hello!
      Please read the above article carefully. To calculate the sum by conditions, you can find useful information in this article - Excel SUMIFS and SUMIF with multiple criteria.

      =SUMPRODUCT(--(A1:A4>=DATE(2023,1,1)),--(A1:A4<=DATE(2023,3,31)),--(B1:B4="Revenue1"),--(C1:C4="Kansas"),D1:D4)

      =SUMIFS(D1:D4,A1:A4,">="&DATE(2023,1,1),A1:A4,"<="&DATE(2023,3,31),B1:B4,"Revenue1",C1:C4,"Kansas")

      1. Thank you, Alexander,

        Thank you for the recommendation and guidance; however, how can I get the formula to search first a specific title of a column and then have the research done?
        As an example, the column "type" in the current formula is selected as an array (B1:B4="Revenue1) . Can it be possible to have a condition that search first a specific labeled column (in this case, "type" and then at this time indirectly take a chosen additional criteria in this now found column ("Revenue1") and continue the formula as you explained (-(C1:C4="Kansas"),D1:D4))

        Thank you again for all your help.
        JP

  5. Muchas gracias, me fue de mucha utilidad

  6. Hi, this formula:
    =SUMPRODUCT(--ISNUMBER(SEARCH(B3&" for ",Description)))*Cost

    is returning required value. I want to add a second criteria to return value based on date range. So I changed formula to:
    =SUMPRODUCT(--(ISNUMBER(SEARCH(B3&" for ",Description))),--(Date,">="&$D$1,Date,"<="&EOMONTH($D$1,0))*Cost)

    Unfortunately, formula is returning #VALUE. It looks like the date part of formula is generating the #VALUE error. Could you take a look please?
    Your help is greatly appreciated.

    1. Hello!
      Use this example to add a date range to the SUMPRODUCT function

      =SUMPRODUCT(--(A1:A10>=$D$1),--(A1:A10<=EOMONTH($D$1,0)))

      I hope it’ll be helpful.

      1. Thanks for help.

  7. Hello, I am interested in adding a formula to my excel to reflect if this cells has a specific month or date range, to sum this column.

    ex:
    A B C D E
    CC-234-10-31-2022 VIROLOGY 101001 VIROLO 10/31/2022 235.17
    CC-234-10-12-0222 PARASITOLOGY 101001 PARAST 10/12/2022 324.00
    CC-234-10-11-0222 PARASITOLOGY 101001 PARAST 10/11/2022 324.00
    CC-234-10-10-0222 PARASITOLOGY 101001 PARAST 10/10/2022 324.00
    CC-234-10-09-0222 PARASITOLOGY 101001 PARAST 10/9/2022 324.00
    CC-234-10-03-0222 PARASITOLOGY 101001 PARAST 10/3/2022 324.00

    if column D has a date between 10/1/2022 and 10/31/2022 (or just the month 10; whichever is easier), sum up column E.

  8. Is it possible to use the filter feature to replace the condition cells, eg instead of getting the condition from E1:H5 above, replace with filter result from A1:C1
    Meaning I would like to have sumproduct but my conditions are very dynamic

  9. Manager Name : Daily Attendance'!$F$4:$F$154
    Dates: Daily Attendance'!$G$4:$AK$4
    B3=Manager Name, B5= date
    Daily attendance of the resources marked as (Present(P), Absent(A), Approved Leave(AL), Unplanned Leave(UL) etc..) in Daily Attendance'!$g$5:$AK$154 range

    =SUMPRODUCT(('Daily Attendance'!$F$4:$F$154=B5)*('Daily Attendance'!$G$4:$AK$4=$B$3),(COUNTIFS(G$4:G$154,"P")+COUNTIFS(G$4:G$154,"PNS")))

    Here, (COUNTIFS(G$4:G$154,"P")+COUNTIFS(G$4:G$154,"PNS"))) this is not a number value hence formula isn't working.

    Kindly help me to get count of attendance marked from Daily Attendance'!$g$5:$AK$154 range for a particular manager for a specific date

    1. Thank you for your quick response Alexander !

    2. Sorry, below is the range from which I am trying to get the count of Present (P) + PNS
      (COUNTIFS('Daily Attendance'!$G$4:$AK$154,"P")+COUNTIFS('Daily Attendance'!$G$4:$AK$154,"PNS")))

    3. Hi!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets. If you have a specific question about the operation of a function or formula, I will try to answer it.

  10. Hello. I want to use sumproduct with multiple criteria but the value of one criterion might be located in multiple rows. Ideally I would like to use something such as :
    =SUMPRODUCT(($C$50:$DQ$50="Theoretical")*($C$11:$DQ$122=$H8)*($H$11:$H$122=Summary!D$6),'Sheet1 (62)'!$C$11:$DQ$122)
    but the ($C$11:$DQ$122=$H8) makes the formula to give 0. Is there any other way of searching that H8 value in multiple rows and columns?

    1. Hello!
      All ranges in the SUMPRODUCT formula must be the same size. You cannot multiply matrices with different numbers of rows or columns.

      1. Hello again. They have the same range. The table is from C11 till DQ122. My question is if it is possible to apply criteria within many columns AND rows instead for either rows or columns

          1. But when I put the formula below it is not giving an error:
            =SUMPRODUCT(($C$50:$DQ$50="Theoretical")*($C$11:$DQ$11=$H7)*($H$11:$H$122=D$2),$C$11:$DQ$122) while the H$11:H$122 is not the same as C$50:DQ$50. But they refer to only one column or only one row

            1. Hi!
              I am not sure I fully understand what you mean. Please clarify your issue or provide additional information to understand what you need.

              1. Hi again,

                Thanks for your help. I found another way of dealing with that issue. Basically, I have some sheets with tables similar to the table below. I was setting a formula such as =SUMPRODUCT(($A$1:$F$8)*($A$1:$A$8=$A2)*($A$1:$F$8=B1)) in order to find the 1a,2a,3a etc values. The issue is that the rows with the header "6,7,8" are not at the same row always. Therefore, I cannot set the row 5 as the range for my criterion. The criterion ($A$1:$F$8=B1) is giving an error as it needs to be only 1 row instead of a range of rows. I still don't know though if something like that is possible but I hope my problem is more clear now.

                1 2 3 4 5
                a 1a 2a 3a 4a 5a
                b 1b 2b 3b 4b 5b
                c 1c 2c 3c 4c 5c
                6 7 8
                a 6a 7a 8a
                b 6b 7b 8b
                c 6c 7c 8c

  11. Hello!
    I need some help also :)
    I have a huge table with:
    Columns:
    - two columns with parameters: ColSupplier = supplier, ColProject = Project
    - many columns with weekly production outputs (1 column / week) - so I have 3 lines of parameters to define one column: year (LinYear) + quarter (LinQtr) + week number

    I need to sum up per supplier and per project the production output by quarter or by year.
    I have managed to reach out to the right "first cell" cells with "INDEX/MATCH function":
    =INDEX(DataTable,MATCH(1,(ColSupplier="Supplier 1"*(ColProj="Project A"),0), MATCH(1,(LinYear="2023")*(LinQtr="Q2"),0)))
    But I need to sum up all production outputs over the full quarter (up to 13 weeks = 13 columns), and not only revert the first weekly output Excel is finding. I try adding SUM in front of INDEX but it does not work.

    Thanks in advance for your support!
    Best regards
    Serge

    1. Hello!
      I hope this example formula will show you the solution using your data. Multiply the conditioned matrix and the data matrix and use the SUM function to calculate the quarterly sum.

      =SUM(((A4:A10="Supplier 1")*(B4:B10="Project A")) * ((C2:G2="Q2")*(C1:G1=2023)) * C4:G10)

      I hope I answered your question. If something is still unclear, please feel free to ask.

      1. Great, it is working perfectly, thanks a lot!

  12. I have hundreds of rows of data on a worksheet. I can use filters to glean the counts of items, but I have used sumproduct to do this in the past. Unfortunately, I lost the complex formula string when I changed computers. I am attempting to get a count of items that match multiple criteria in the columns. For example, see below, I am attempting to gather a count of items using the Name, Class, Location, and Style. I recall using "--" between the SUMPRODUCT functions to narrow down the retrieved data and displayed it as the output. For example, using SUMPRODUCT I would like to get the count of Names that are a specific Location that have a specific Size. Thank you ...

    Name | Class | Location | Size

    Joe | Third | AZ | Large

    Bill | First | NJ | Med

    Francis | Third | AZ | X-Large

    Harry | First | NV | Med

      1. Hello Alexander!

        Thank you! The tutorial provided the information required to complete the task in Excel.

  13. Hello!

    I have multiple columns and rows that I need a count for. Going by the above example, I was looking for a formula / method that counts the below
    1. ALL regions (North + South) and ONLY Lemons
    2. ONLY North region and ALL items (Apples + Lemons + Oranges)
    3. ALL regions and ALL items

    The input requirement of region and fruit specification (whether region is All, North, or South; whether item is All, Apples, Lemons, Oranges) can be flexible using a filter.

    Thanks for your help!

  14. Hello Alexander,

    I need help please.

    I have 3 columns (A-B-C):
    A has 12 records both text and numerical;
    B has 10,000 records TEXT format;
    C has 10,000 records TEXT and NUMERIC format;

    I need to COUNT records in the column "B", IF column "C" MATCH column "A". As you can see, both Column "B" and "C" have a direct correlation (in other words it is one table).

    Please note, the COUNT formula cannot be referenced each individual "CELL" 12 times from the column "A". I will need to copy the formula to other files, and sometimes there will be 5 records in column "A", sometimes 15, or even 25. The idea for this formula is to change the "RANGE" only, based on column "A" records from one file to another.

    Any help is greatly appreciated.

    1. Hello!
      To count the number of records by multiple criteria, use the SUMPRODUCT function:
      =SUMPRODUCT(--ISNUMBER(MATCH($C$1:$C$100,$A$1:$A$30,0)), --($B$1:$B$100<>""))

      Hope this is what you need.

      1. Unfortunately, I could not find any simple examples using MATCH with SUMPRODUCT.

        Thank you Alexander, your formula worked.

  15. Hello all,

    I'm trying to do a stack ranking based on performance that are weighted differently. I have 9 measurements that fall into three groupings, Management, Wallet share, and quality. Should I sum the stack ranking of each grouping to get the weighted score and then use the sumproduct formula so that I can get a total stack ranking?

    The weighting is 30% Management, 30% wallet, and 40% for quality.

  16. Thank You very much.

  17. Hello - I am trying to calculate the 75th percentile of a weighted average. When I combine the percentile and sumproduct, it seems to only return the sumproduct. The values I am using are 1) column with number of incumbents; and 2) the salary for each. In the example below, I believe the answer should be $118,206, but sumproduct keeps returning the answer of $123,278 - this is the weighted average, but I need the 75th percentile of the weighted average. Thanks.

    Inc Salaries
    17 111,043
    1 111,298
    1 118,206
    1 135,200
    1 151,388
    1 162,136
    1 163,865
    1 166,462
    1 185,661

    1. Hello!
      You can only calculate the percentile from one column of values. Use PERCENTILE function.
      You can also use this formula.

      =RANK.EQ(B1,$B$1:$B$9,1)/COUNT($B$1:$B$9)

      Write it in C1 and copy it down the column to calculate all rank percentiles.

  18. I am trying to figure out how to use the SUMPRODUCT formula to count the number of times an event happens within each month (JAN-DEC). If the value from another sheet cites FRONT (Front door), it will add it as part of a total within that month
    =SUMPRODUCT((Data!C662:C2002="FRONT"))
    OR
    =SUMPRODUCT((MONTH(Data!A662:A2002)=1)*(YEAR(Data!A662:A2002)=2022)*(Data!E662:E2002="FRONT"))

    1. Hello!
      You are making mistakes in the syntax of the SUMPRODUCT function. Read carefully the first paragraph of this article. Also convert logical expressions to numbers. This is also described above.

      =SUMPRODUCT(--(MONTH(Data!A662:A2002)=1),--(YEAR(Data!A662:A2002)=2022),--(Data!E662:E2002=”FRONT”))

      Do you have dates and text in the same column?

  19. Hi sir,

    I'm trying to use sumproduct formula as vba code putting values in place of cell reference, but it's not working.
    Can you tell me best way using values

  20. 18 37 422.73
    1 2 1006.08
    2 1 811.62
    3 2 1352.71

    Hi - I am trying to do a sumproduct of A and C plus a sumproduct of 50% of the values in B and C, but I do not want to create a new column showing the 50%. The answer with the numbers above would be 24,881.695. I really hope this is possible!! THANK YOU!

    1. Hi!
      Please try the following formula:

      =SUMPRODUCT(A1:A4,C1:C4) + 0.5*SUMPRODUCT(B1:B4,C1:C4)

      I hope it’ll be helpful.

      1. THANK YOU!!

    2. I'm so sorry - I read this and it confused me. Trying again! :)

      18 37 422.73
      1 2 1006.08
      2 1 811.62
      3 2 1352.71

      I want to do

      sumproduct($A$1:$A$4,$C$1:$C$4)+sumproduct($C$1:$C$4,((0.5*$B$1:$B$4))

      but this gives an answer I do not expect (21,444.89) instead of 24,881.695.

  21. 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

  22. 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

    1. 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.

  23. 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

    1. 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.

      1. Hi,
        Thank you for your answer, I will try that.

  24. 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,

    1. 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.

  25. 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))

  26. 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!

  27. 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?

      1. 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.

  28. 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

    1. 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.

  29. 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.

    1. 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.

  30. 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

    1. 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.

  31. 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.

    1. 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.

      1. 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.

        1. 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?

          1. 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.

              1. Hello!

                Problem solved! Thank you so much for your assistance.

  32. very nice

  33. 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)

  34. 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

  35. 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.

  36. Hi, I want to make an excel sheet with daily wages and extra hours pay summation for my employees, individually.

    1. 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.

  37. 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.

    1. 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.

  38. 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!

  39. 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) ))

  40. 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

  41. 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

  42. good explanation and we are learning this excel in this page

  43. Thanks for you help

  44. IF(SUMPRODUCT(--(F$26>=Inputs!$B$2:$B$22),--(F$260,1,"")
    I want particular column value instated of 1 in this formula

  45. IF(SUMPRODUCT(--(F$26>=Inputs!$B$2:$B$8343),--(F$260,1,"")
    I want particular column value instated of 1 in this formula

  46. 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.

  47. 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

  48. Nice work. Very helpful

  49. 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,

  50. 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....

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)