The Complete Guide to SUMPRODUCT in Excel: From Beginner to Advanced

SUMPRODUCT is one of those Excel functions that looks intimidating at first, gets explained poorly in most places, and then suddenly clicks, after which you wonder how you ever worked without it.

This guide starts from absolute zero and builds all the way to complex real-world uses. It explains the logic behind the function, not just the syntax. It covers every major use case, every common mistake, every quirk that causes wrong results, and the situations where you should use something else instead. No prior knowledge of array formulas required.

SUMPRODUCT works in every version of Excel, including Excel 2007 and later, Excel 2019, Excel 2021, and Excel 365. Most examples in this guide work across all versions.

What SUMPRODUCT Actually Does

Before any syntax, here is the plain idea.

Imagine you have a shopping list. Five items, each with a quantity and a price. To find the total cost, you multiply quantity by price for each row, then add up all those results. That is literally all SUMPRODUCT does. It multiplies corresponding values across two or more lists, then sums everything up.

Without SUMPRODUCT, you would write something like:

=(A2*B2)+(A3*B3)+(A4*B4)+(A5*B5)+(A6*B6)

With SUMPRODUCT:

=SUMPRODUCT(A2:A6, B2:B6)

Same result. One formula instead of five. And if you have 500 rows instead of 5, the formula stays exactly the same length.

That is the core. But SUMPRODUCT becomes genuinely powerful once you understand how it handles logic, conditions, and Boolean values, which is what the rest of this guide covers.

The Syntax

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

array1 is required. It is a range of cells or an array of values.

array2, array3, … are optional. Up to 255 arrays can be provided in total, though practical formulas rarely use more than four or five.

Each array must have the same number of rows and the same number of columns. If they do not match, SUMPRODUCT returns a #VALUE! error. This is the most common beginner mistake and is covered in detail in the troubleshooting section.

How SUMPRODUCT Works Internally

Understanding what Excel is doing behind the scenes is what separates someone who can copy SUMPRODUCT formulas from someone who can write them from scratch.

When you write =SUMPRODUCT(A2:A6, B2:B6), Excel does this:

1. Takes the first value in A2 and multiplies it by the first value in B2.
2. Takes the second value in A3 and multiplies it by the second value in B3.
3. Repeats for every row.
4. Adds all the results together and returns a single number.

The key phrase is “corresponding values.” Excel always matches positions: first with first, second with second, and so on. This positional matching is what makes SUMPRODUCT so powerful, and it is also what causes problems when your arrays are different sizes or shapes.

You can verify this logic by pressing F9 on a selected portion of the formula inside the formula bar. Excel will show you the intermediate array of values before summing them.

Your First SUMPRODUCT Formula

Say you have the following data:

Item	Quantity	Price
Apples	10	0.50
Bread	3	2.00
Milk	4	1.20
Eggs	12	0.25

To get the total spend without a helper column, enter:

=SUMPRODUCT(B2:B5, C2:C5)

Excel calculates: (10×0.50) + (3×2.00) + (4×1.20) + (12×0.25) = 5 + 6 + 4.80 + 3 = 18.80

That is a basic two-array SUMPRODUCT. Now let us go deeper.

Using SUMPRODUCT with a Single Array

If you pass only one array to SUMPRODUCT, it simply sums everything in that array. No multiplication involved.

=SUMPRODUCT(B2:B5)

This returns the same result as =SUM(B2:B5). Not useful on its own, but it is a stepping stone to understanding conditional SUMPRODUCT, where a single array contains the result of a calculation.

Arithmetic Operators Inside SUMPRODUCT

SUMPRODUCT does not only multiply. You can use any arithmetic operator inside the formula: addition, subtraction, multiplication, and division. The formula applies the operation row by row, then sums the results.

For example, if you want total revenue minus a discount per item:

=SUMPRODUCT((B2:B5 * C2:C5) – D2:D5)

This calculates revenue for each row, subtracts the discount for that row, then sums everything. A full row-level calculation in one formula.

Or to calculate profit margin in one step:

=SUMPRODUCT((C2:C5 – D2:D5) * B2:B5)

Subtract cost from price for each item, then multiply by quantity, then sum.

When using arithmetic operators like this, you are essentially passing a single array (the result of the arithmetic) to SUMPRODUCT. SUMPRODUCT just sums it.

The Real Power: Using SUMPRODUCT for Conditional Sums and Counts

This is where most guides either rush through the explanation or get it wrong. Take your time here because this logic underpins everything else.

Introducing Boolean Logic in SUMPRODUCT

Suppose you have sales data and you want to sum only the sales from the “North” region.

Region	Sales
North	500
South	300
North	700
East	200
North	400

You want the sum of all rows where Region = “North”.

When you write a condition like A2:A6="North" inside Excel, it does not return a single true or false. It returns an array of true and false values, one for each row:

{TRUE, FALSE, TRUE, FALSE, TRUE}

The problem is that TRUE and FALSE are not numbers. You cannot multiply them directly against sales figures in a way that returns meaningful results in all cases.

This is where the two main approaches diverge, and where confusion lives.

The Double Negative (–) Approach

The double negative operator converts TRUE to 1 and FALSE to 0:

=SUMPRODUCT(–(A2:A6=”North”), B2:B6)

What happens step by step:

1. A2:A6="North" creates {TRUE, FALSE, TRUE, FALSE, TRUE}
2. -- converts this to {1, 0, 1, 0, 1}
3. SUMPRODUCT multiplies this against {500, 300, 700, 200, 400}
4. Result: (1×500) + (0×300) + (1×700) + (0×200) + (1×400) = 1600

The double negative is called the “double unary operator” in technical terms. The first negative converts TRUE to -1 and FALSE to 0. The second negative flips -1 back to 1. The net effect is TRUE becomes 1 and FALSE becomes 0.

Other ways to achieve the same conversion:

• Multiply by 1: (A2:A6="North")*1
• Add zero: (A2:A6="North")+0
• Use N(): N(A2:A6="North")

All produce the same result. The double negative is simply the most common convention.

The Asterisk (*) Approach

The second approach uses the multiplication operator:

=SUMPRODUCT((A2:A6=”North”) * B2:B6)

When you multiply a Boolean (TRUE/FALSE) by a number using *, Excel automatically converts TRUE to 1 and FALSE to 0. So the double negative is not needed when you are multiplying arrays together.

This works the same as the double negative version in most cases, but there is an important difference when the sum range contains text values. If any cell in B2:B6 contains text rather than a number, the asterisk version returns #VALUE!, while the comma-separated double negative version typically handles it without error.

Which should you use?

For AND logic with numeric sum ranges, both work. The asterisk approach is slightly more compact. The comma approach with double negatives is more robust when your data might contain text or blanks. The practical recommendation is to use the asterisk approach for AND conditions and use the comma approach when you are not 100% sure your sum range is clean numeric data.

This distinction is explained further in the troubleshooting section.

AND Logic: Multiple Conditions

To add multiple conditions with AND logic (meaning all conditions must be true), use multiplication to combine them:

=SUMPRODUCT((A2:A10=”North”) * (C2:C10=”Q1″) * B2:B10)

This sums sales where Region is “North” AND Quarter is “Q1”. Each condition creates a 0 or 1 array. Multiplying them together means a row only contributes to the total if all conditions are 1. A single 0 in any condition multiplies the entire row to 0.

You can chain as many AND conditions as you need:

=SUMPRODUCT((A2:A100=”North”) * (C2:C100=”Q1″) * (D2:D100>1000) * B2:B100)

Three conditions: Region = North, Quarter = Q1, and a threshold of over 1000. All three must be true for a row to be included.

OR Logic: Any Condition Can Be True

OR logic is handled with addition instead of multiplication:

=SUMPRODUCT(((A2:A10=”North”) + (A2:A10=”South”)) * B2:B10)

This sums sales where Region is “North” OR “South”. Addition combines the two Boolean arrays. If a row matches either condition, the sum becomes 1 (or 2 if it matches both, more on this below). Multiplying by the sales range includes those rows.

The double-counting problem with OR logic in different columns

OR logic becomes tricky when conditions apply to different columns and a row might satisfy both. If a row matches condition 1 in column A AND condition 2 in column B, the OR addition gives 2 instead of 1, which doubles that row’s contribution to the sum.

The fix is to wrap the OR array in the SIGN function before multiplying:

=SUMPRODUCT(SIGN((A2:A10=”North”) + (B2:B10=”Premium”)) * C2:C10)

SIGN converts any positive number to 1, zero to 0, and negative to -1. So both a value of 1 (one condition met) and 2 (both conditions met) become 1. The row is counted once regardless of how many conditions it satisfies.

For OR conditions within a single column (same as the North/South example above), a row can only match one value, so double-counting cannot happen and SIGN is not needed.

Counting Instead of Summing

SUMPRODUCT can count rows that match conditions, not just sum values.

To count how many rows have Region = “North”:

=SUMPRODUCT(–(A2:A10=”North”))

This produces an array of 1s and 0s and then sums them. The result is a count. This is equivalent to =COUNTIF(A2:A10, "North") but becomes useful when you need to count with multiple AND conditions without using COUNTIFS, or when you need conditions that COUNTIFS cannot handle.

To count rows matching two AND conditions:

=SUMPRODUCT((A2:A10=”North”) * (C2:C10=”Q1″))

No explicit sum range needed. The multiplication of two Boolean arrays gives 1 only where both are true, and SUMPRODUCT sums those 1s.

Weighted Averages

The weighted average is one of SUMPRODUCT’s most practical and widely used applications. A regular average treats every value equally. A weighted average accounts for the fact that some values represent larger quantities than others.

A classic example: you have five products, each with a unit price and a quantity sold. The average price is not simply AVERAGE(prices) because a product sold in large quantities should influence the average more than one sold rarely. The weighted average price is:

=SUMPRODUCT(prices, quantities) / SUM(quantities)

With real ranges:

=SUMPRODUCT(B2:B4, C2:C4) / SUM(C2:C4)

This multiplies each price by its quantity (total revenue per product), sums those values (total revenue), then divides by total quantity to get the revenue-weighted average price.

Important: the weights do not need to add up to 100% or to 1. SUMPRODUCT handles any weighting scale. Whether your weights are percentages, raw counts, or priority scores, the formula structure is the same.

Conditional weighted average

To get the weighted average only for a specific group, combine a condition with the weighted average structure:

=SUMPRODUCT(–(A2:A10=”North”), B2:B10, C2:C10) / SUMIF(A2:A10, “North”, C2:C10)

Numerator: sum of price × quantity for North rows only.Denominator: total quantity for North rows only.Result: the weighted average price for the North region specifically.

Counting Unique Values

Before Excel 365 introduced the UNIQUE function, SUMPRODUCT was the standard way to count distinct values in a list. Even in Excel 365, the technique is valuable in older workbooks or when UNIQUE is not appropriate.

The formula to count unique values in a range:

=SUMPRODUCT(1/COUNTIF(A2:A10, A2:A10))

Here is how it works. COUNTIF with a range as both the range and criteria argument returns an array showing how many times each value appears. If “North” appears three times, every North row gets a 3. SUMPRODUCT then divides 1 by each count: 1/3 for each North row gives three values of 0.333, which sum to 1. Each unique value contributes exactly 1 to the total regardless of how many times it appears.

Warning: this formula fails if the range contains blank cells, because COUNTIF returns 0 for blanks, and dividing by zero causes an error. The safe version adds a blank check:

=SUMPRODUCT((A2:A10<>””) / COUNTIF(A2:A10, A2:A10&””))

The &"" forces COUNTIF to count blanks as a consistent empty string, preventing the division by zero.

Partial Text Matching with SUMPRODUCT and SEARCH

SUMPRODUCT does not support wildcard characters directly. You cannot write =SUMPRODUCT(--(A2:A10="North*"), B2:B10) and expect it to work the way SUMIF wildcards do.

For partial text matching, combine SUMPRODUCT with SEARCH and ISNUMBER:

=SUMPRODUCT(ISNUMBER(SEARCH(“North”, A2:A10)) * B2:B10)

SEARCH returns the position of the search text within each cell (a number), or an error if not found. ISNUMBER converts those results to TRUE (found) or FALSE (not found). The multiplication operator coerces those to 1 and 0, and SUMPRODUCT sums the result.

SEARCH is case-insensitive. If you need case-sensitive matching, use EXACT instead:

=SUMPRODUCT(–(EXACT(“North”, A2:A10)) * B2:B10)

EXACT returns TRUE only when the text matches exactly, including case.

Using SUMPRODUCT Across Multiple Sheets

SUMPRODUCT can reference ranges on different sheets in the same workbook. The syntax follows the standard Excel cross-sheet reference:

=SUMPRODUCT(Sheet2!A2:A10 * Sheet2!B2:B10)

For conditional SUMPRODUCT across sheets:

=SUMPRODUCT((Sheet2!A2:A10=”North”) * Sheet2!B2:B10)

Note that SUMPRODUCT cannot be used to sum across a 3D range (like Sheet1:Sheet3!A2:A10) the way SUM can. Each sheet must be referenced individually.

SUMPRODUCT vs SUMIFS: When to Use Which

Now that SUMIFS exists in all current Excel versions, the question of when to use SUMPRODUCT versus SUMIFS comes up constantly. Here is the honest, practical answer.

Use SUMIFS when:

• You need a straightforward sum with multiple AND conditions using exact matches, numeric comparisons, or date ranges.
• You are working with a large dataset (tens of thousands of rows) and calculation speed matters. In tested comparisons, SUMIFS on 150,000 rows with 500 formulas takes roughly 2.7 seconds. SUMPRODUCT with the same setup takes roughly 13 seconds, nearly five times slower.
• Your sum range and criteria ranges are not required to be the same size (SUMIFS allows different-sized ranges, SUMPRODUCT does not).

Use SUMPRODUCT when:

• You need OR logic within a single formula.
• You need to combine conditions with calculations (like weighted sums or revenue minus discount).
• You need partial text matching with SEARCH or ISNUMBER.
• You need case-sensitive matching with EXACT.
• You need to count or sum distinct values.
• You are on Excel 2003 or earlier and SUMIFS does not exist.
• Your conditions involve array operations that SUMIFS cannot express in a single formula.

SUMPRODUCT is more flexible. SUMIFS is faster for simple multi-condition sums. Use the right tool for the job rather than a preference for one over the other.

Common Errors and How to Fix Them

#VALUE! Error

The most common SUMPRODUCT error. There are three main causes.

Arrays are different sizes. Both arrays must have the same number of rows and the same number of columns. If you write =SUMPRODUCT(A2:A6, B2:B4) the first array has 5 rows and the second has 3 rows. This returns #VALUE! immediately. Check that all your ranges start and end at the same row numbers.

Text in the sum range combined with the asterisk syntax. If your sum range contains any text values (including numbers formatted as text), and you are using the multiplication operator syntax, SUMPRODUCT cannot multiply text by a number and returns #VALUE!. Switch to the comma-separated syntax with double negatives:

This fails if B2:B10 has any text:

=SUMPRODUCT((A2:A10=”North”) * B2:B10)

This handles text values gracefully:

=SUMPRODUCT(–(A2:A10=”North”), B2:B10)

The comma syntax treats text in the sum range as zero rather than throwing an error.

Error values in the referenced ranges. If any cell in your arrays contains #DIV/0!, #REF!, #N/A, or another error, SUMPRODUCT will return that error. The first error encountered in the calculation is what gets returned. Use IFERROR to handle this:

=SUMPRODUCT(–(A2:A10=”North”), IFERROR(B2:B10, 0))

This replaces any error in the sum range with zero, allowing the rest of the calculation to proceed.

Wrong Results with No Error Message

These are harder to diagnose because the formula runs without complaint but returns incorrect values.

The January blank cell problem. This is one of the most frequently asked-about SUMPRODUCT gotchas. If you use SUMPRODUCT with the MONTH function to count or sum by month, you will almost certainly get incorrect results for January.

The reason: when Excel’s MONTH function encounters a blank cell, it interprets it as the date serial number 0, which corresponds to January 0, 1900. The MONTH of that date is 1, which is January. So every blank cell in your date range gets counted as January, even though those cells contain no date.

This formula overcounts January on any range with blank cells:

=SUMPRODUCT(–(MONTH(A2:A8)=1))

The fix is to exclude blank cells explicitly:

=SUMPRODUCT((A2:A8<>””) * (MONTH(A2:A8)=1))

Or more robustly:

=SUMPRODUCT((ISNUMBER(A2:A8)) * (MONTH(A2:A8)=1))

This is a well-known quirk that trips up experienced Excel users and is almost never mentioned in SUMPRODUCT guides.

OR double-counting across different columns. Covered earlier in the OR logic section. If both conditions can be true for the same row, a plain addition gives 2 for that row, not 1. Use SIGN to cap the result at 1.

Mixed data types preventing matches. If one range contains numbers and the comparison value is stored as text (or vice versa), the condition A2:A10=B1 may return FALSE for every row even when the values look identical. This happens frequently with data imported from external systems where numbers get stored as text. Convert both sides to the same type before comparing, or use VALUE() or TEXT() to normalize.

Mismatched range orientation. SUMPRODUCT works across rows. If you accidentally supply a horizontal range (a row) where a vertical range (a column) is expected, the results will be wrong. Make sure all your array ranges span the same rows.

Whole Column References and Performance

You can use whole column references like A:A in SUMPRODUCT since Excel 2007, but you should not. A column in Excel has 1,048,576 rows. SUMPRODUCT with whole column references evaluates all of them, even if only 100 contain data. This can make a workbook noticeably slow, especially when multiple SUMPRODUCT formulas use whole column references.

Always use bounded ranges like A2:A1000 instead of A:A. If your data grows, extend the range as needed.

Advanced Uses

Comparing Two Columns Row by Row

SUMPRODUCT can count how many rows meet a comparative condition between two columns:

=SUMPRODUCT(–(B2:B10 > C2:C10))

This counts how many rows have a value in column B that exceeds the corresponding value in column C. Useful for things like counting how many sales people exceeded their target.

SUMPRODUCT with Date Ranges

To sum values within a date range:

=SUMPRODUCT((A2:A100 >= DATE(2024,1,1)) * (A2:A100 <= DATE(2024,12,31)) * B2:B100)

This sums column B for all rows where the date in column A falls within the year 2024. The DATE function constructs an exact date value so the comparison works correctly regardless of how dates are formatted in the cells.

To sum by both month and year (avoiding the January blank problem):

=SUMPRODUCT((ISNUMBER(A2:A100)) * (YEAR(A2:A100)=2024) * (MONTH(A2:A100)=3) * B2:B100)

The ISNUMBER check excludes blank cells before MONTH or YEAR ever evaluates them.

Using SUMPRODUCT to Rank Without Ties Distortion

A lesser-known use is counting how many items in a list are larger than a given value, which is functionally a rank:

=SUMPRODUCT(–(B2:B10 > B2)) + 1

This counts how many values in B2:B10 are greater than the value in B2, then adds 1. The result is the rank of B2 within the list. Unlike the RANK function, this approach can be combined with conditions to produce conditional rankings.

SUMPRODUCT to Sum Every Nth Row

To sum every second row (odd rows):

=SUMPRODUCT((MOD(ROW(A2:A20)-ROW(A2)+1, 2)=1) * A2:A20)

MOD returns the remainder of division. Rows where the row number minus the starting offset is divisible by 2 with no remainder are even positions. Changing the 2 to 3 sums every third row, and so on.

SUMPRODUCT with VLOOKUP or XLOOKUP

You can embed VLOOKUP inside SUMPRODUCT to perform lookup-based conditional sums. For example, to sum sales where a product’s category (looked up from another table) matches a target:

=SUMPRODUCT((VLOOKUP(A2:A100, CategoryTable, 2, 0)=”Electronics”) * B2:B100)

VLOOKUP runs for each row in the array, returns the category, and the condition filters to “Electronics” rows. SUMPRODUCT sums the results.

Note that embedding VLOOKUP in SUMPRODUCT over large ranges can be slow. XLOOKUP behaves similarly and is generally preferred in Excel 365 and 2021.

SUMPRODUCT and Older Excel Versions

SUMPRODUCT has been available since very early versions of Excel, including Excel 97. This makes it uniquely valuable for workbooks that need to remain compatible with older Excel installations.

Before COUNTIFS and SUMIFS were introduced in Excel 2007, SUMPRODUCT was the primary method for multi-condition counting and summing. Analysts who learned Excel in the early 2000s often have a strong preference for SUMPRODUCT-based formulas because of this history.

One important behavior difference in older versions: using whole column references (A:A) in SUMPRODUCT before Excel 2007 produces an error. Always use bounded ranges for backward compatibility.

In Excel 2021 and 365, some problems SUMPRODUCT previously solved can now be solved more elegantly with FILTER, UNIQUE, and other dynamic array functions. The comparison section at the end of this guide addresses when to consider those alternatives.

SUMPRODUCT vs Dynamic Array Functions in Excel 365

If you are on Excel 365 or Excel 2021, some SUMPRODUCT use cases have cleaner alternatives:

Counting distinct values: The classic SUMPRODUCT(1/COUNTIF(...)) approach can now be replaced with =COUNTA(UNIQUE(A2:A100)) which is simpler and handles blanks more cleanly.

Filtering and summing: A complex multi-condition SUMPRODUCT can sometimes be replaced with SUM(FILTER(...)), which is easier to read and modify.

However, SUMPRODUCT remains irreplaceable for weighted averages, OR logic in a single formula, partial text matching without helper columns, and any situation where you need a single-cell result without a spill. These scenarios still require SUMPRODUCT or SUMIFS combinations that cannot be replicated cleanly with dynamic arrays alone.

Quick Reference

Task	Formula Pattern
Sum product of two ranges	SUMPRODUCT(range1, range2)
Conditional sum (one condition)	SUMPRODUCT(--(range=value), sum_range)
Conditional sum (AND logic)	SUMPRODUCT((cond1)*(cond2)*sum_range)
Conditional sum (OR logic, same column)	SUMPRODUCT(((cond1)+(cond2))*sum_range)
Conditional sum (OR logic, different columns)	SUMPRODUCT(SIGN((cond1)+(cond2))*sum_range)
Conditional count	SUMPRODUCT(--(range=value))
Weighted average	SUMPRODUCT(values, weights)/SUM(weights)
Count unique values	SUMPRODUCT(1/COUNTIF(range, range))
Partial text match	SUMPRODUCT(ISNUMBER(SEARCH(text, range))*sum_range)
Sum by month and year (safe)	SUMPRODUCT(ISNUMBER(SEARCH(text, range))*sum_range)

Frequently Asked Questions

What does SUMPRODUCT do in Excel?

SUMPRODUCT multiplies corresponding values across two or more arrays, then returns the sum of all those products. Its power lies in the fact that the “arrays” can be conditions or logical tests, not just numeric ranges, which lets it count and sum based on multiple criteria in a single formula.

What is the difference between SUMPRODUCT and SUMIFS?

Both can sum values based on multiple conditions. SUMIFS is faster for straightforward multi-condition sums with exact or range comparisons. SUMPRODUCT is more flexible and handles OR logic, weighted calculations, partial text matching, and row-level arithmetic that SUMIFS cannot do in a single formula. For simple conditional sums over large datasets, prefer SUMIFS.

Why do I need the double negative (–) in SUMPRODUCT?

When a SUMPRODUCT formula contains a logical condition like A2:A10="North", Excel evaluates it as an array of TRUE and FALSE values. In some syntax patterns, SUMPRODUCT does not automatically convert these to 1 and 0, causing incorrect results. The double negative forces the conversion: TRUE becomes 1 and FALSE becomes 0. When using the asterisk multiplication operator between conditions, the conversion happens automatically. When using the comma separator, the double negative is required.

Why does my SUMPRODUCT formula return zero for January?

Blank cells in a date range are treated as the date serial number 0 by Excel’s date functions, and MONTH of 0 is January (month 1). This causes every blank cell to be counted as a January date. Fix this by adding a blank check before the MONTH condition: =SUMPRODUCT((ISNUMBER(A2:A100))*(MONTH(A2:A100)=1)).

Why does SUMPRODUCT return #VALUE!?

The two most common causes are mismatched array sizes (all arrays must have the same number of rows and columns) and text values in the sum range when using the asterisk multiplication syntax. Switch to the comma-separated syntax with double negatives to handle text values without errors.

Can SUMPRODUCT use wildcards?

No. SUMPRODUCT does not support wildcard characters directly. For partial text matching, use ISNUMBER(SEARCH("text", range)) inside SUMPRODUCT instead. SEARCH is case-insensitive. For case-sensitive matching, use EXACT instead of SEARCH.

Is it safe to use whole column references in SUMPRODUCT?

It is technically allowed in Excel 2007 and later, but strongly not recommended. SUMPRODUCT evaluates every row in the column, all 1,048,576 of them, which can dramatically slow down recalculation. Always use bounded ranges that match your actual data.

When should I use SUMPRODUCT instead of just SUM or COUNTIF?

Use SUMPRODUCT when you need to combine conditions with calculations (like multiplying two ranges before summing), when you need OR logic within a single formula, when you need partial text matching, or when you need a weighted average. For simple sums or counts with straightforward conditions, SUM, SUMIF, SUMIFS, and COUNTIFS are simpler and faster.