SUMPRODUCT WITH IF CRITERIA/CONDITIONS – COUNT and SUM ARRAYS Examples
When to use SUMPRODUCT IF
In your spreadsheets, you might use SUMPRODUCT IF in the following situations:
Comparing large values
You can use SUMPRODUCT IF to calculate and compare these numbers when using spreadsheets to collect a large number of values. For instance, you could use a spreadsheet’s SUMPRODUCT function to gather data on your home’s electrical efficiency. Every electrical bill paid over the course of a year and the results from the previous years’ bills could be included in one column. The first column would then be calculated by adding the results, and SUMPRODUCT IF would allow you to segment your data into particular subsets to make them simpler to compare visually.
For instance, you could use the SUMPRODUCT IF function to determine the total amount of overpaid bills for clients who are more than six months past due if you have a database of bill payments. Adding up your monthly expenses can help you compare your income and create a budget that works for you.
Finishing financial paperwork
When calculating financial paperwork for their clients, accountants and other financial experts may use SUMPRODUCT IF. For instance, you could use SUMPRODUCT IF to determine a customer’s debits and credits over the course of the year. By ensuring that they return the client’s anticipated financial result or their most recent bank information, you will be able to compare the two data subsets. Multiple SUMPRODUCT IF formulas may be used by accountants in a single spreadsheet, especially when comparing various information sources. For instance, when comparing a client’s annual savings to their savings from prior years, they might use several SUMPRODUCT IF formulas.
Performing extensive statistical research
SUMPRODUCT IF may be helpful for statisticians’ spreadsheets when gathering information and data for their various studies. They might, for instance, add multiple statistical columns to their SUMPRODUCT IF formula, compute and compare them by dividing them into particular data subsets, and so on. These statistical studies could compare energy consumption before and after the installation of energy-efficient windows and doors, holiday spending patterns, or political polling data. When creating intricate charts and graphs, they can categorize their data by using the IF function to create specific subsets.
Combining multiple information sources
When evaluating multiple information sources on your worksheets, you can incorporate SUMPRODUCT IF. This procedure might involve gathering data from several data columns and comparing it to forecasts. You can compute these results using SUMPRODUCT IF, compare them to one another, and get the desired result. Additionally, based on these totals, you can select particular spreadsheet results. For instance, you could instruct the function to send the result to a particular spreadsheet cell. Instead of manually adding this information later, doing so allows you to automatically input it.
What is SUMPRODUCT IF?
If some of the data points meet a certain criterion, the SUMPRODUCT IF function, which combines the SUMPRODUCT and IF functions, applies logical expressions to multiple data ranges. For instance, you could use SUMPRODUCT to check sales goals and the IF function to compare the outcome to certain date ranges by comparing the revenue generated in February to that of November.
The program highlights the information to perform the function on and displays the results in the cell you specify based on the criteria you specify for the IF function. When calculating intricate financial or sales data, experts may incorporate multiple SUMPRODUCT IF functions into their spreadsheets.
How to use SUMPRODUCT IF
When adding a SUMPRODUCT IF function to your spreadsheets, follow these steps. When using one criterion for your SUMPRODUCT IF function, these steps are especially helpful:
1. Understand the proper syntax
The Excel SUMPRODUCT IF function has the following syntax:
=SUMPRODUCT(IF(criteria range=criteria, values range1*values range2))
With this formula, you can input the data types you want to compare and later incorporate the if function. Choose the cells you want to enter into the formula and select the column you want to calculate in. As an illustration, your spreadsheet might have columns for product prices and quantity. Use this information when creating your SUMPRODUCT IF formula.
2. Set up your tables
You can make a second table after the one you already have with the data you want to use, starting in cell A1, to give your function a place to return values. Your primary table, for instance, might have three columns and four rows. The headings for your data, such as item, total price, and units sold, may appear in your first row. Shoes, Books, and Dresses products may be listed under the item, and the corresponding sales information will appear in the following columns.
Your new table might have two columns and four rows to fulfill your function, with the rows reflecting the data from the first table. This new table’s second column might be labeled “Total Sales,” and this is where you would use the SUMPRODUCT IF function.
3. Input your array and criterion into your formula
You would enter the criteria as the cell in the second table that includes your item’s name if you wanted to calculate the total sales for dresses using the table above. For instance, if you were calculating the sales of dresses, you might type E4 into your formula’s criteria field in cell F4. You would instruct the function to multiply the corresponding values in the total price and units sold columns after having it search through your entries in row A for the value in E4, which is “Dresses.” This function would look like:
“=SUMPRODUCT(IF($A$2:$A$4=E4,$B$2:$B$4*$C$2:$C$4))”
According to this function, Excel will multiply the value in the corresponding row from cells B2 to B4 by the value in the corresponding row from cells C2 to C4 if there is a value in cells A2 to A4 that equals the value in E4.
4. Add multiple criteria for your needs
To calculate equivalent results, copy and paste this formula into as many cells as necessary. For instance, the formula in the third step only calculated the dresses in the fourth row. Using the value in cell E2 of the second table as the criterion, you can modify the formula to calculate “Shoes” as follows:
=SUMPRODUCT(IF($A$2:$A$4=E4,IF($A$2:$A$4=E2,$B$2:$B$4*$C$2:$C$4)))
To complete your spreadsheet calculations, add as many additional criteria as you need to the formula.
Example of SUMPRODUCT IF
Heres an example to consider when calculating SUMPRODUCT IF:
You have a sales spreadsheet that begins with your headings in the first row and has the names of your products in column A, their sales totals in column B, and the sales price in column C, as shown in the example below:
NamesSales NumbersPriceBaby bottles605. 00Diapers20010. 00Pacifiers533. Then, you create a second table to the side with the identical product names in a column and a column for “Total Sales” to the right. Your SUMPRODUCT IF function would look like this if you wanted to determine your sales of pacifiers and use the value “Pacifiers” in cell E4 as the criteria in cell F4 of your second table:
=SUMPRODUCT(IF($A$2:$A$4=$E4,$B$2:$B$4*$C$2:$C$4))
This equation would determine whether any values in column A are equal to the word “Pacifiers” in cell E4. The function multiplies the value in B4 by the one in C4 (53 times 3), because A4 meets the criteria. You would get an output of 159 in cell F4, which indicates that you sold $159 worth of pacifiers during that time.
Please note that Indeed is not affiliated with any of the businesses mentioned in this article.
FAQ
Is there a SUMPRODUCT if function?
Although Excel doesn’t have a built-in “SUMPRODUCT IF” function, you can use the “SUMPRODUCT” and “IF” functions together to create an array formula.
How do I use SUMPRODUCT with multiple criteria?
Use the plus sign (+) between the arrays to conditionally add or subtract cells using the OR logic. The plus sign functions as the OR operator in Excel SUMPRODUCT formulas and array formulas, telling Excel to return TRUE if ANY of the conditions in an expression evaluate to TRUE.
