I am creating a spreadsheet to track sales and calculate commissions. I have four columns Product1, Product2, Product3 and Warranty Term.

Product1 has three levels of service (not including "blank").
Product2 has four levels of service (not including "blank").
Product3 has one level of service (not including "blank").
Warranty Term has four options: 0,1,2,3 (yrs.)

I need a simple sum of each possible outcome of a customer's order. E.g. Sum of orders with Product1 (any level of service) with a 2yr. Warranty Term. or Sum of orders with Product1 (any level of service) and Product2 (any level of service) with a 3yr. Warranty Term.

I have tried a little bit of every formula to no avail, but I am far from an Excel guru.

Howdy, and welcome to the board.

For one criteria, you use SUMIF.

However, if more than one criteria, then there are a couple ways to do it.

1. Use SUMPRODUCT

2. USE SUM(IF as an array formula.

Each has advantages, but I think when first starting SUMPRODUCT might be more understandable.

Thank you for the warm welcome. I have tried SUM(IF as an array as well as the Conditional Sum (even using the wizard) to no avail. I'll post what I've got. If someone can make it work, I would be soooooo grateful!

Mr. Incredible


Won't work. Why will it not upload the excel file???

You have to provide a zip file.