ExcelCSE.com

Menu
  • Excel 101
  • Excel Functions
  • Excel Formula
  • Contact Us
How to Use Excel > Excel Formula > SUMPRODUCT IF – Multiple Criteria SUMPRODUCT

SUMPRODUCT IF – Multiple Criteria SUMPRODUCT

SUMPRODUCT IF - a multiple criteria SUMPRODUCT
Table of contents :
  • Question 1 – No Criteria
  • Question 2 – One Criteria
  • Question 3 – Multiple AND Criteria Same Column
  • Question 4 – Multiple OR Criteria Same Column
  • Question 5 – Multiple AND Criteria Different Column
  • Question 6 – Multiple AND/OR Criteria

SUMPRODUCT IF

There is no SUMPRODUCTIF function in excel, but you can use the SUMPRODUCT function to solve one criteria or multiple criteria questions, in the same column or different columns.

For example, there are data such as the image below

SUMPRODUCT Multiple Criteria

Question 1 – No Criteria

What is the total amount of money earned from the sales transaction above?

The Criteria

  • No Criteria

The Formula

=SUMPRODUCT(D2:D16,E2:E16)

It’s easy, use the SUMPRODUCT function, and you get the total of money earned.

The Result

Excel SUMPRODUCT IF

Question 2 – One Criteria

What is the total money earned from selling Apple smartphones?

The Criteria

  1. Manufacturer = “Apple”

The Formula

=SUMPRODUCT(--(B2:B16="Apple"),D2:D16,E2:E16)

The Result

Conditional SUMPRODUCT

Question 3 – Multiple AND Criteria Same Column

What is the total money earned from sales on August 7, 2018, to August 9, 2018?

The Criteria

  1. Date >= “8/7/2018”
  2. Date <= “8/9/2018”

The Formula

=SUMPRODUCT(--(A2:A16>=DATEVALUE("8/7/2018"))
,--(A2:A16<=DATEVALUE("8/9/2018"))
,D2:D16,E2:E16)

The Result

SUMPRODUCT with Criteria

Question 4 – Multiple OR Criteria Same Column

What is the total money earned from selling Samsung and Apple smartphones?

The Criteria

  1. Manufacturer = “Samsung”
  2. Manufacturer = “Apple”

The Formula

=SUMPRODUCT(--((B2:B16="Samsung")+(B2:B16="Apple")>0)
,D2:D16,E2:E16)

The Result

SUMPRODUCT with Conditions

Question 5 – Multiple AND Criteria Different Column

What is the total money earned from selling Apple smartphones from August 7, 2018, to August 9, 2018?

The Criteria

  1. Date >= “8/7/2018”
  2. Date <= “8/9/2018”
  3. Manufacturer = “Apple”

The Formula

=SUMPRODUCT(--(A2:A16>=DATEVALUE("8/7/2018"))
,--(A2:A16<=DATEVALUE("8/9/2018"))
,--(B2:B16="Apple")
,D2:D16,E2:E16)

The Result

SUMPRODUCT IF Statement

Question 6 – Multiple AND/OR Criteria

What is the total money earned from selling Apple and Samsung smartphones before August 7, 2018 and after August 9, 2018?

The Criteria

  1. Date < “8/7/2018”
  2. Date > “8/9/2018”
  3. Manufacturer = “Samsung”
  4. Manufacturer = “Apple”

The Formula

=SUMPRODUCT(--((B2:B16="Samsung")+(B2:B16="Apple")>0)
,--((A2:A16<DATEVALUE("8/7/2018"))+(A2:A16>DATEVALUE("8/9/2018"))>0)
,D2:D16,E2:E16)

The Result

SUMPRODUCT IF Statement

There is another solution to answer the questions above. Please read the following articles as an alternative solution to the same question.

  • SUMIF Multiple Criteria
  • SUMIFS Limitations and Solutions

Related Function

Function used in this article
  • Excel SUMPRODUCT Function
  • Excel AND Function
  • Excel OR Function
Share
Tweet
Google+
Email
Prev Article
Next Article

Related Articles

How to Subtract Multiple Cells from One Cell in Excel
For example, there are data such as the image below. …

How to Subtract Multiple Cells from One Cell in Excel

How to Add and Subtract Percentage in Excel
Add Percentage in Excel What is the price of each …

How to Add/Subtract Percentage in Excel

SUMIF Multiple Criteria
For example, there are data such as the image below. …

SUMIF Multiple Criteria

How to Do Division in Excel
How to divide in Excel? For example, there is data …

How to Do Division in Excel

How to Do Multiplication in Excel
For example, there is data like the picture below, four …

How to Do Multiplication in Excel

Tags:AND Function Conditional SUMPRODUCT Excel SUMPRODUCT IF OR Function SUMIF SUMPRODUCT SUMPRODUCT Function SUMPRODUCT IF SUMPRODUCT If Statement SUMPRODUCT Multiple Criteria SUMPRODUCT Multiple Criteria Same Column SUMPRODUCT vs SUMIFS SUMPRODUCT With Conditions SUMPRODUCT With Criteria

Categories

  • Excel 101
  • Excel Formula
  • Excel Functions

Recent Posts

  • Get the Original Price from Percentage Increase/Decrease
  • How to Add/Subtract Percentage in Excel
  • How to Extract Day, Month and Year from Date in Excel
  • How to Add Seconds, Minutes and Hours to a Time in Excel
  • How to Add Days, Weeks, Months and Years to a Date in Excel
  • How to Calculate Time Difference in Excel
  • Calculate Number of Days, Weeks, Months and Years Between Two Dates in Excel
  • Flash Fill Excel
  • Paste Special Excel
  • Excel TEXTJOIN Function
  • Excel CONCAT Function
  • Excel CONCATENATE Function
  • Excel REPLACE Function
  • Excel SUBSTITUTE Function
  • Excel REPT Function
  • Excel FIND Function
  • Excel SEARCH Function
  • Excel MID Function
  • Excel RIGHT Function
  • Excel LEFT Function

ExcelCSE.com

The Ultimate Guide How to Use Excel Better
Copyright © 2021 ExcelCSE.com
Contact Us - Privacy Policy - Sitemap | Theme by MyThemeShop.com

Ad Blocker Detected

Our website is made possible by displaying online advertisements to our visitors. Please consider supporting us by disabling your ad blocker.

Refresh