conditional sum based on two variables

[Dave Lagergren]

I have a spreadsheet that calculates commissions. Commissions for some types
of items are based on a tiered structure. Commissions are not paid until our
company has been paid by our carriers. The spreadsheet I am working with
determines the type of transaction (accessory sale, activation, upgrade, data
sale) for each line of each invoice and saves it in column P. In column O we
place a "y" when the company has been paid. Column G has the number of
units. I need to total up the number of units for activation, upgrade and
data sale transactions. I can have hundreds of transactions per rep per
month.

I need to total up how many activations each rep has that we have been paid
for. The same with upgrades and data sales. I have tried numerous
variations of sumif but nothing generates the results I need.

 

[Liz]

Not sure what version of Excel you are using, I have noted that Excel 2007
allows for 2 variables in the SUMIF formula where Excel 2003 does not.

I have used concatenations of 2 fields together as a substitute for not
being able to use 2 variables thus writing the SUMIF against a new column
which is a concatenation of the 2 variables I needed.

 

[Jim Thomlinson]

SumProduct will do what you want. Check out this link...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html