I need something like a combination of sumproduct and sumif.

[joeq]

Hi All,

I've used both SUMIF and SUMPRODUCT.
But now I seem to need the abilities of both.

I need SUMIF's ability to match a particular column to decide whether SUMMING is called for, but also SUMPRODUCT's ability to take the 2 ranges, multiply them and return a product.

SUMIF(A20:A58, BX3, H20:H58)

SUMPRODUCT(H20:H58, D20:D58)

I've tried simulating this with a series of multiplications within IF statemants, but I get the message 'Formula too long' about halfway through the 38 additions I need to complete the formula.

Any help would be greatly appreciated.

Joe

 

[scifibum]

Hi Joe,

I'm sure you can do this with just SUMPRODUCT.

What you want to do is add an expression as one of your ranges in the SUMPRODUCT formula.

If I have model numbers in column A, I only want to use rows where the model number is "500", and the two columns I want to conditionally SUMPRODUCT are B and C, then this is the formula:

=SUMPRODUCT(--(A2:A100="500"),B2:B500,C2:C500)

For more information about the "--" syntax view this thread:

http://www.mrexcel.com/board2/viewtopic.php?t=116148

The reason this works is the first expression returns an array of ones and zeros. Only the rows where the condition is true return the 1, and all other rows are multiplied by zero, which essentially means they are ignored in the sum of products.

Hope that helps.

 

[scifibum]

I was very lazy there and didn't adapt the example you provided in your post. Bad me. I'll try now.

Your example:

SUMIF(A20:A58, BX3, H20:H58)

SUMPRODUCT(H20:H58, D20:D58)

Converted to a single sumproduct formula:

=SUMPRODUCT(--(A20:A58=BX3),H20:H58,D20:D58)

 

[joeq]

Keith,

Thanks - thats perfect.

joe