The "plus sign" (+) is for OR in array formulas (and SUMPRODUCT).
=Sumproduct((A3:A159=B3:B159)*((D3:D159="Target A")+(D3:D159="Target B")))
With SUMPRODUCT, every comparison is evaluated to TRUE and FALSE. In Excel, TRUE can be represented by any non-zero number, while FALSE is equivalent to 0. If D3="Target A", the first element of that array will be 1 (True=1). That means that D3 cannot be Target B and that element of the that array will be 0. When you add them together, it will be 1, and so will be counted as TRUE.
It can be tough to get your head around how arrays work in formulas. Try reading this.
Answer from dkusleika on Stack ExchangeExceljet
exceljet.net › home › formulas › sumproduct with if
SUMPRODUCT with IF - Excel formula | Exceljet
January 6, 2023 - To create a conditional sum with the SUMPRODUCT function you can use the IF function or use Boolean logic. In the example shown, the formula in H5 is: =SUMPRODUCT(IF(C5:C16="red",1,0),D5:D16,E5:E16) The result is $750, the total value of items ...
Reddit
reddit.com › r/excel › can you use =if logic in a sumproduct formula?
r/excel on Reddit: Can you use =IF logic in a SUMPRODUCT formula?
September 23, 2023 -
Can somebody clever please help me with a formula for the below, or let me know if there is a better way to set this up.
In cell E5, I would like to do a =SUMPRODUCT formula for columns D and E, but only when column F = F5.
Thank you!
Top answer 1 of 3
16
=SUMPRODUCT(D14:D25,E14:E25,--(F14:F25=F5))
2 of 3
1
u/ZacatecanJack - Your post was submitted successfully. Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread. Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. Include your Excel version and all other relevant information Failing to follow these steps may result in your post being removed without warning. I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
How do I build an =IF statement to SUM above values, up to a certain cell? Oct 14, 2016
r/excel 9y ago
Using SUMIFS to return a value if certain text found in one column or another column, or both columns. May 9, 2023
r/excel 3y ago
How to write an IF formula to return the sum of some numbers from a table column but not all? Jul 21, 2023
r/excel 3y ago
Adding a IF to a SUMIF and SUMProduct formula
I have a number of sumif, sumpoduct and countif formulas which all work fine. Example: =SUMIFS($A$2:$A$1000,$B$2:$B$1000,"RETAIL", $C$2:$C$1000,"HIGH") I now need to add an optional filter to the formula which basically says If A1=YES, then add this to the formula $D$2:$D$1000,"TRUE" If... More on mrexcel.com
20
0
September 23, 2025SUMPRODUCT or product within SUMIF with multiple criterion and IF statements.
I made some improvements and I found a way to get the sumproduct. However I still having some trouble with the nested If statements. More on mrexcel.com
1
0
March 2, 2023excel - SUMPRODUCT function with IF logic - Stack Overflow
I am attempting to multiple two arrays, but only if array 2 meets a greater than or equal to criteria. That criteria is >=243 | Array 1 | Array 2 | | 5 | 200 | | 5 | 24... More on stackoverflow.com
Sumproduct + IF or and
Hello, I need to apply the sumproduct formula on a specific country; however, my issue is that the sumproduct is being applied on all the countries. This is a simple example: I want to do the sumproduct only for Panama for example, so I do an advanced… More on learn.microsoft.com
6
0
07:39
Using SUMPRODUCT With The IF Function To Create A Conditional Formula ...
08:08
SUMPRODUCT Excel (Including 3 Examples) - YouTube
05:33
How to use SUMPRODUCT WITH IF CONDITION(S) in EXCEL - YouTube
14:58
🎈 How to Use SUMPRODUCT with Multiple Criteria in Excel | Use ...
Sum a Product in Microsoft Excel Using the Function ...
Stack Overflow
stackoverflow.com › questions › 76629182 › add-if-statement-to-sumproduct-formula
excel - Add IF Statement to SUMPRODUCT Formula - Stack Overflow
I am currently using a SUMPRODUCT formula to count the occurrences by month and year of an event. I have copied this below: =SUMPRODUCT((MONTH(Sheet2!$G$2:$G$10000)=E17)*(YEAR(Sheet2!$G$2:$G$10000)...
A.CRE
adventuresincre.com › home › the conditional weighted average – sumproduct with sumif (updated july 2025)
The Conditional Weighted Average – SUMPRODUCT with SUMIF (Updated July 2025) - Adventures in CRE
July 28, 2025 - Most likely, you’d tell me to manually sort the tenants into tenant type groups and then use a SUMPRODUCT & SUM formula to calculate the weighted average rent by tenant size [ = SUMPRODUCT(Tenant Size Column, Tenant Rent Column)/SUM(Tenant Size Column)]. Now, you wouldn’t be wrong with this answer, but there’s a slick way to do this calculation without having to manually sort the tenants into groups or customize the formula for specific tenants.
Microsoft Support
support.microsoft.com › en-us › excel › functions › sumproduct-function
SUMPRODUCT function | Microsoft Support
How to use the SUMPRODUCT function in Excel, one of Excel’s math and trig functions, using multiplication, addition, subtraction, and/or division to return the sum of the products of corresponding ranges or arrays.
MrExcel
mrexcel.com › forums › question forums › excel questions
Adding a IF to a SUMIF and SUMProduct formula | MrExcel Message Board
September 23, 2025 - =SUMPRODUCT($A$2:$A$1000,SIGN(($N$2:$N$1000<>$O$2:$O$1000)+($G$2:$G$1000<>"BOX"))*($H$2:$H$1000="MANAGER")*($I$2:$I$1000="EXPIRED")*(ISNUMBER(SEARCH($B$1,$Z$2:$Z$1000)))*(IF(A1="YES",$D$2:$D$1000="ALL",1))) For the FILTER one I can't see how $D$2:$D$1000 relates to SHEET1 rows 2 to 20000?
Excelcomplete
excelcomplete.com › help › excel-sumproduct-function
SUMPRODUCT Explained: Examples and Formulas
The SIGN function will return 1 if a number is positive, 0 if 0, and -1 if negative. So, in this example, applying SIGN to the array would transform 2 into a 1. Below is an example of this formula. You can click “Show Steps” to visualize how Excel will evaluate each step, namely the SIGN function. ... =SUMPRODUCT(SIGN(({TRUE,FALSE,FALSE,FALSE,TRUE,FALSE,TRUE,FALSE,FALSE,FALSE}+{TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,FALSE,TRUE,FALSE}))*(E5:E14)) //criteria are evaluated
Top answer 1 of 4
10
The "plus sign" (+) is for OR in array formulas (and SUMPRODUCT).
=Sumproduct((A3:A159=B3:B159)*((D3:D159="Target A")+(D3:D159="Target B")))
With SUMPRODUCT, every comparison is evaluated to TRUE and FALSE. In Excel, TRUE can be represented by any non-zero number, while FALSE is equivalent to 0. If D3="Target A", the first element of that array will be 1 (True=1). That means that D3 cannot be Target B and that element of the that array will be 0. When you add them together, it will be 1, and so will be counted as TRUE.
It can be tough to get your head around how arrays work in formulas. Try reading this.
2 of 4
1
There is an OR function in Excel: OR(logical1, [logical2], [logical3], ...)
This example will be TRUE if either C1 OR B1 contain values greater than 100 or FALSE if they both don't:
=OR(B1>100,C1>100)
I'm not 100% sure how you want to apply this, but I'm sure this can help.
ExtendOffice
extendoffice.com › excel tips › excel formulas › use sumproduct with if function in excel
Use SUMPRODUCT with IF function in Excel
September 30, 2024 - =SUMPRODUCT(IF($C$5:$C$14=$G5,$D$5:$D$14*$E$5:$E$14))
Excel Insider
excelinsider.com › home › our blog › excel functions & formulas › how to use sumproduct with if function in excel
How to Use SUMPRODUCT with IF Function in Excel - Excel Insider
October 29, 2025 - You can use this simple SUMPRODUCT with the IF conditionals to filter data by multiple criteria, apply several logical conditionals like OR/AND, and use numeric thresholds. You will explore all these methods, the nested IFs, and automated VBA Macros to level up your skills.
MyExcelOnline
myexcelonline.com › home › how to use sumproduct with if in excel – 3 perfect examples
How to Use SUMPRODUCT with IF in Excel – 3 Perfect Examples
For example, =SUMPRODUCT((condition1)*(condition2)*values) allows for conditional calculations by turning each condition into an array of 1s and 0s. Avoiding Nested IFs: By structuring conditions directly within SUMPRODUCT, you avoid the complexity ...
Published: June 25, 2026
MrExcel
mrexcel.com › forums › question forums › excel questions
SUMPRODUCT or product within SUMIF with multiple criterion and IF statements. | MrExcel Message Board
March 2, 2023 - =SUMPRODUCT(INDIRECT("tableA[var & LEFT(a_cost;FIND(" "narea1) -1) &"]");(tableA[P_ID]=IF([@P_ID]="all";;MATCH([@P_ID];tablec[varname];0)))*(tableA[T_ID]=IF([@T_ID]="all";;VLOOKUP([@T_ID];narea2e;2)))*(tableA[Y_ID]=IF([@Y_ID]="all";;[@Y_ID]))) I want the formula to sumproduct variables in a table A (for now, only one array is specified) based on conditions based on table B.
Exceljet
exceljet.net › home › formulas › sumproduct count multiple or criteria
SUMPRODUCT count multiple OR criteria - Excel formula | Exceljet
July 27, 2022 - To count matching rows with multiple OR criteria, you can use a formula based on the SUMPRODUCT function. In the example shown, the formula in F10 is: =SUMPRODUCT(ISNUMBER(MATCH(B5:B11,{"A","B"},0))* ISNUMBER(MATCH(C5:C11,{"X","Y","Z"},0))) This formula returns a count of rows where the value in column one is A or B and the value in column two is X, Y, or Z.
wmfexcel
wmfexcel.com › 2017 › 06 › 03 › the-amazing-sumproduct
The amazing SUMPRODUCT | wmfexcel
June 4, 2017 - To simulate “AND” conditions into SUMPRODUCT is quite straight forward: Simply multiplying the conditions together would do. As only one single incidence of False (0) would give you a product of False(0). Put it into other words, you will ...
Top answer 1 of 2
1
This is the same as Variatus's post except it avoids the, in my opinion, odd choice to multiply the parameters inside the sumproduct function, which handles the multiplication. I've also explicitly converted the first logical array to number.
=SUMPRODUCT(N(F2:F5>=243),E2:E5,F2:F5)
2 of 2
0
You're almost there. Just add the first array to your formula another time,
=SUMPRODUCT((F2:F5>=243)*(E2:E5)*(F2:F5))
(F2:F5>=243) creates an array of 1 or 0, and the result of that must be multiplied with (F2:F5).
Microsoft Learn
learn.microsoft.com › en-us › answers › questions › 5217983 › help-using-sumproduct-and-if-functions-togeher
Help using SUMPRODUCT and IF functions togeher - Microsoft Q&A
March 30, 2023 - =SUMPRODUCT(('Provider Replacement'!A:A<>"")*('Provider Replacement'!W:W="May")+('Completed Cases'!A:A<>"")*('Completed Cases'!Y:Y="May")) I now want to add a condition to only count if a criteria is met from another column (i.e if Column F = 'New Cohort' then record the number)
ElyxAI
getelyxai.com › home › how-to guides › how to use sumproduct with multiple and conditions
How to How to Use SUMPRODUCT with Multiple AND Conditions in Excel - Practical Guide | ElyxAI
Learn to use SUMPRODUCT with multiple AND conditions to sum values based on several criteria simultaneously. This advanced technique replaces complex nested IF statements, enabling you to calculate conditional totals efficiently across multiple columns without array formulas, making your ...
SimpleSheets
simplesheets.co › blog › beginners-guide-on-sumproduct-with-multiple-criteria-in-excel
How to use SUMPRODUCT with Multiple Criteria in Excel
February 6, 2023 - Sumproduct with multiple criteria is one of the handiest Microsoft Excel functions available to any user. It allows users to add numerical values from two or more data ranges that meet specific criteria.