by 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:
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:
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.
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"))
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:
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?
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 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:
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:
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:
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:
- 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:
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:
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 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:
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)
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:
and turn it into a regular formula:
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)
245 comments
Hi Alexander, Im hoping you can help.
I am thinking I will need to use SUMPRODUCT Formula however, please tell me if I'm wrong.
I have C5 (value) D5 (Month) E5 (markup percent)
I then have F3 (January) G3 (February) H3 (March) etc.
I need F5 to pickup D5 and work out the C5 x E5 /100
F5 being specific to F3 'January' so needing to match that.
Hi! I’m not sure I got you right since the description you provided is not entirely clear. I don't know what is written in F5. To calculate the amounts for January, you can try this formula:
=SUMPRODUCT((C5:C10)*(D5:D10=1)*E5:E10/100)
If this does not help, explain the problem in detail.
Hello Alexander, I am attempting to find the average age of my clients, weighted by revenue they produce. I have attempted to use SUMPRODUCT with and IF, AND qualifier, to select for just those clients on a certain code (denoted in cell B14) and by a second qualifier that is in cell b11. Here is what I have:
SUMPRODUCT(IF(AND('Client List'!$D$2:$D$1000='7TEC'!$B$14)('Client List'!L2:L1000='7TEC'!B11),(('Client List'!$K$2:$K$1000*'Client List'!$G$2:$G$1000)/'7TEC'!F$26)))
When I calculate, I keep getting zero. Any help you might provide in sumproduct calculations with two qualifying conditions would be greatly, greatly appreciated.
Hi! I can't check a formula that contains unique references to your data, which I don't have. Note that there is no comma between AND conditions in the formula.
I am currently using =SUMPRODUCT(($F$3:$F$8="A")*($E$3:$E$8="22/02/24)*$G$3:$G$8) for daily Total. But I'm looking to create another not monthly, Meanting I need to change the single date to a date range, for example >01/02/24 <29/02/22.
Would you be able to assist?
Hi! Add another condition as described in the article above. The date can be set using the DATE function.
=SUMPRODUCT(($F$3:$F$8="A")*($E$3:$E$8>=DATE(2024,2,1))*($E$3:$E$8<=DATE(2024,2,29))*$G$3:$G$8)
To find the sum by condition, you can also use the COUNTIFS function. You can find the examples and detailed instructions here: How to use SUMIFS in Excel - formula examples.
=SUMIFS($G$3:$G$8,$F$3:$F$8,"A",$E$3:$E$8,">="&DATE(2024,2,1),$E$3:$E$8,"<="&DATE(2024,2,29))
Hello,
I am searching for a suitable formula to split out headcount between different shows in our organization. I feel like sumproduct or a version of it would be what I need but I cant figure it out.
Normally I would use countifs to obtain headcount information for a specific week to show HC under specific show/department. But it only works if I have employee scheduled under one show.
Is there any version of sumproduct that would recognize how many "*/*" there is in a schedule and say if someone is scheduled under show1/show5/show8, then formula assigned 0.33 for every of the shows within that week. And if there was no / i nthe text, it would be 1 as a headcount.
Thanks,
Mi.
Hi! You can count the number of "/" characters in a text string using the formula below. Consecutively extract each character from the text using the MID function and compare it with the "/". Use the SUM function to count the matches.
=SUM(--(MID(A1,ROW(A1:A100),1)="/"))
Thank you Alexander,
I was thinking about it, but how will this count actual shows? Because the % depends on how many shows someone is scheduled between. but then the actual show code determines where either full headcount or partial goes to.
Example:
Column_1 | Column_2 | Column_3 | Column_4
Employee ID | Department | 11.27.2023 | 12.4.2023
123456 | HR | Dev/HR | HR
123455 | IT | Dev/Training | DEV
123466 | DEV | Dev/Training | DEV
123465 | HR | HR/Training | Training
In this case I have 4 employees - thus should have 4 HC each week.
SHOW(TASK)| 11.27 | 12.4
HR | 1 | 1
DEV | 1.5 | 2
Training | 1.5 | 1
But using =SUM(--(MID(A1,ROW(A1:A100),1)="/")) it will only show me 1 and 0 depending on how many "/" there were in a specific week.
Hi! The formula I sent to you was created based on the description you provided in your first request. However, as far as I can see from your second comment, your task is now different from the original one.
Try this formula:
=SUM((ISNUMBER(SEARCH($H$1,C1:C4)))/(ISNUMBER(SEARCH("/",C1:C4))+1))
For more information, please read: How to find substring in Excel
H1 -- "HR"
Thank you for a suggestion. Unfortunately it did not work.. It gives me "Error!
Is there any way I could upload an excel example?
In the "Conditionally count / sum / average cells with multiple criteria" section, what if instead of "apples" you had multiple colors in front of the word "apple" in the same cell; so "Red Apples", "Golden Apples", etc. How could you write the formula to tally any version of the word "apple" that occurs?
In a regular SUMIF you can do this with quote star word star quote, eg. SUMIF($B$2:$B$12,"*Apples*",$C$2:$C$12), however I tried this with SUMPRODUCT and it did not work. Any suggestions?
Hi! If I got you right, the formula below will help you with your task:
=SUMPRODUCT(--ISNUMBER(SEARCH("apple",B2:B12)),C2:C12)
For more information, please read: How to find substring in Excel
That worked! Thank you!
Dear Sir,
Can you help me to fix the sumproduct result to get (i.e.32.49%) mentioned below working.
I have want to get the sumproduct function with following serious
Project Cost (A1); Variation(B1); % of Profit(C1)
50000 3000 25%
80000 8000 37%
Total 130000 11000 32.49% (How to get this result 32.49% in function)
Thanks & Regards,
Sunil Pinto
Hi! I assume you want to get a weighted average value. I couldn't get the result you want, but you can try these formulas yourself: How to calculate weighted average in Excel.
I have a table where several values are generated daily, such as is shown (actual data extends to many more days)
1 A B C D E F G .......
2
3 S M T W T F S ........
4
5 3 4 10 4 8 6 6
6 4 7 4 6 4 5 4
7 5 3 10 5 6 3 7
8 6 5 6 8 5 7 8
I would like to create a formula that can find which day's 4 numbers contains
the smallest (and largest) sum, preferably without using a helper cell to post the sums.
In the example above, cells A5:A8 for Sunday (3 4 5 6 = 18) would be smallest,
cells C5:C8 for Tuesday (10 4 10 6 = 30) would be largest.
I would like to have a running account of smallest and largest that
continually updates itself as each day's 4 numbers are added to the array.
If the smallest could be shown, I can figure out the largest from that.
Any help would be greatly appreciated ! Thanks !
Hello
I am not getting the desired outcome with below mentioned formula;
=SUMPRODUCT(SUMIFS(Raw!K:K,Raw!$D:$D,Summary!$F19,Raw!$C:$C,(Summary!$F$5:$F$13)*($E$5:$E$13=TRUE),Raw!G:G,(Summary!$C$19:$C$64)*(Summary!$E$19:$E$64=TRUE)))
In this, (Summary!$C$19:$C$64) is a Text so the product of this array is giving error, Is there any other way to run this condition.
Hi! It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work. But it is not possible to summarize text values.
Hello Again
Please check, I have made it easier to understand
=SUMPRODUCT(SUMIFS($C$2:$C$16,$A$2:$A$16,($F$2:$F$4)*($G$2:$G$4=TRUE),$B$2:$B$16,($H$2:$H$7)*($I$2:$I$7=TRUE)))
In This ($H$2:$H$7) range contains Text, I have entered this formula in J2 Cell. In column 1 there are headers.
Pls refer below data set I have used as an example.
A B C F G H I
92 SD01 10 91 TRUE SD01 TRUE
93 SD01 8 92 FALSE SD02 TRUE
93 SD03 7 93 FALSE SD03 FALSE
91 SD06 10 SD04 FALSE
92 SD03 7 SD05 TRUE
93 SD02 7 SD06 FALSE
92 SD05 5
93 SD05 9
92 SD02 10
93 SD03 10
91 SD04 5
91 SD03 7
93 SD04 10
91 SD03 5
92 SD01 8
Hi! In the SUMIFS formula, all ranges must be the same size. We have written about this many times in the blog. The SUMIFS function does not work with arrays. Therefore, you cannot use the formula ($F$2:$F$4)*($G$2:$G$4=TRUE), which returns an array. You multiply "SD01" by TRUE. What result do you expect to get?
Got it, Thanks.
It would be great help if you can suggest any other formula to get the result.
I can't guess what you want to do and what result you need to get.
Sumproduct function SUMPRODUCT(MAX((E2:E10=M2)*(F2:F10))) works for maximum value extraction. If you replace MAX by MIN result is zero.
Is there any non array formula to find the minimum. I am not keen on using MIN IF
Hi! The formula below will do the trick for you:
=SUMPRODUCT(MIN(IF((E2:E10=M2)*(F2:F10)>0,(E2:E10=M2)*(F2:F10),"")))
Hi,
I want to nest a ranged LEFT function into a SUMIFS formula, but am getting either a #SPILL and/or #VALUE error, I believe SUMPRODUCT should help but I'm not sure how. Here is my formula:
=SUMIFS(A1:A100,B1:B100,"B",C1:C100,"C",D1:D100,LEFT(D1:D100,1)="D")
To be clear, I want to return a single value that is the sum of all values in the range A1:A100 that meet three criteria, but excel wants to return 100 #VALUE errors spilling down the sheet. Thoughts?
Thank you!
Hi! As I have written many times before, the SUMIFS function cannot use other functions as arguments. Use the SUMPRODUCT function for your task. Try this formula:
=SUMPRODUCT(A1:A100,--(B1:B100="B"),--(C1:C100="C"),--(LEFT(D1:D100,1)="D"))
I want the offset in it. How will this happen?
=SUMPRODUCT(--(C5="Kalim"),H4-G5+F5+E5-D5)+SUMPRODUCT(--(C5="Ali Sir"),H4+G5+F5+E5)
Sorry, it's not quite clear what you are trying to achieve.
Hi Ablebits Team:
I have a "SUMPRODUCT with multiple criteria" challenge in Excel:
I have filter criteria in Columns E and F
And I need to calculate the weighted average of the values in Columns S and U.
All columns have the same number of rows.
I can figure out the formula for one criterion using SUMPRODUCT-IF: =(SUMPRODUCT(IF(E$37:E$250000=11,U$37:U$250000*(S$37:S$250000/S$8))))*1000
But I can't figure out the formula in cases where there are two criteria...
Can you help?
Thanks in advance...
EP
Hi! There is no standard SUMPRODUCT-IF function in Excel. Explain in detail what you want to do. Give an example of the source data and the desired result.
I recommend reading this guide: How to calculate weighted average in Excel.
=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"
Hi!
Unfortunately, your formula will not work.
In the article 3-D reference in Excel: reference the same cell or range in multiple worksheets you can find list of functions supporting 3D references.
Try to use the recommendations described in this article: SUMIF multiple columns with one or several criteria in Excel. I hope it’ll be helpful.
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!
Hi!
If I understand the problem correctly, it will be helpful for you to study this manual: Excel reference to another sheet or workbook (external reference).
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
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.
Works like a champ!
Looks so simple now that I see the solution
Thank you again
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.
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")
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
Muchas gracias, me fue de mucha utilidad
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.
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.
Thanks for help.
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.
Hello!
To calculate the sum of values in a date range, use this tutorial: SUMIF with dates.
This should solve your task.
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
Hi!
The information you provided is not enough to understand your case and give you any advice, sorry.