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
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
Question 2 – One Criteria
What is the total money earned from selling Apple smartphones?
The Criteria
- Manufacturer = “Apple”
The Formula
=SUMPRODUCT(--(B2:B16="Apple"),D2:D16,E2:E16)
The Result
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
- Date >= “8/7/2018”
- 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
Question 4 – Multiple OR Criteria Same Column
What is the total money earned from selling Samsung and Apple smartphones?
The Criteria
- Manufacturer = “Samsung”
- Manufacturer = “Apple”
The Formula
=SUMPRODUCT(--((B2:B16="Samsung")+(B2:B16="Apple")>0) ,D2:D16,E2:E16)
The Result
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
- Date >= “8/7/2018”
- Date <= “8/9/2018”
- 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
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
- Date < “8/7/2018”
- Date > “8/9/2018”
- Manufacturer = “Samsung”
- 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
There is another solution to answer the questions above. Please read the following articles as an alternative solution to the same question.
