Excel SUMPRODUCT function with formula examples

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

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

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

Excel SUMPRODUCT function - syntax and uses

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

The syntax of the SUMPRODUCT function is simple and straightforward:

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

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

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

The maximum number of arrays is 255 in Excel 365 - 2007, and 30 in earlier Excel versions.

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

Notes:

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

Basic usage of SUMPRODUCT in Excel

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

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

=SUMPRODUCT(A2:A4,B2:B4)

The following screenshots shows it in action:
Excel SUMPRODUCT function - basic usage

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

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

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

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

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

Tip. If you want to only multiply the numbers in each row without adding up the products, then use one of the formulas to multiply columns in Excel.

How to use SUMPRODUCT in Excel - formula examples

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

SUMPRODUCT with multiple criteria

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

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

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

or

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

Where C2:C10 are real sales and B2:B10 are planned sales.
The SUMPRODUCT formula with one criterion to compare arrays.

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

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

Or, you can use the following syntax:

=SUMPRODUCT((C2:C10<B2:B10)*(A2:A10="apples"))
The SUMPRODUCT formula with multiple criteria to compare arrays.

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

How SUMPRODUCT formula with one condition works

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

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

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

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

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

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

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

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

How SUMPRODUCT formula with multiple conditions works

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

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

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

or

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

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

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

Conditionally count / sum / average cells with multiple criteria

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

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

1. SUMPRODUCT formula with AND logic

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

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

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

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

    or

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

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

    or

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

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

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

To add more flexibility to your SUMPRODUCT formulas, you can specify the desired Region and Item in separate cells, and then reference those cells in your formula like shown in the screenshot below:
SUMPRODUCT formulas with AND logic to conditionally count, sum and average cells

How SUMPRODUCT formula for conditional sum works

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

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

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

An intermediate result of the above formula are the following 3 arrays:
An intermediate result of the SUMPRODUCT formula for conditional sum

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

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

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

Example 2. SUMPRODUCT formula with OR logic

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

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

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

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

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

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

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

The following screenshot shows a similar formula in action:
SUMPRODUCT formulas with OR logic to conditionally count and sum cells

Example 3. SUMPRODUCT formula with AND as well as OR logic

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

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

Another way is using the Excel SUMPRODUCT function where:

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

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

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

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

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

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

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

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

To make the formulas a bit more compact, you can type the variables in separate cells - Region in F1 and Items in F2 and H2 - and refer to those cells in your formula:
SUMPRODUCT formula with AND as well as OR logic to conditionally sum cells

SUMPRODUCT formula for weighted average

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

The generic SUMPRODUCT weighted average formula is as follows:

SUMPRODUCT(values, weights) / SUM(weights)

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

=SUMPRODUCT(B2:B7,C2:C7)/SUM(C2:C7)
SUMPRODUCT formula for weighted average

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

SUMPRODUCT as alternative to array formulas

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

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

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

{=SUM(LEN(range))}

and turn it into a regular formula:

=SUMPRODUCT(LEN(range))

SUMPRODUCT as alternative to array formulas

For practice, you can take these Excel array formulas and try to re-write then using the SUMPRODUCT function.

Excel SUMPRODUCT - advanced formula examples

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

Practice workbook for download

Excel SUMPRODUCT examples (.xlsx file)

Table of contents

232 comments

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

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

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

                Problem solved! Thank you so much for your assistance.

  4. very nice

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

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

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

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

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

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

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

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

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

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

  15. Thanks for you help

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

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

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

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

  20. Nice work. Very helpful

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

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

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

  24. Thank you for this tip.

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

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

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

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

  29. Thank you very much...

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

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

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

  33. Thanks for teaching me the sumproduct formula in details

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

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

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

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

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

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

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

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

  42. =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?

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

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

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

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

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

  48. #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?

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

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

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 :)