sumif/sumproduct

[katie1343]

I would like to add up the numbers in a column (ee) if there is a 1 entered in two other columns (L and FP). I know that sumif will not work for this, but I cannot get another formula to work. So far I have:

=SUMPRODUCT(($L$5:$L$1818="1")*($FP$5:$FP$1818="1")*EE5:EE1818)

I do not know what function to use here. Sumproduct came up on the searches I did on previous posts but I cannot get it to work right. In any case I am trying to add, not multiply... Any help on this would be appreciated.

[Paul]

Hi Katie,

Your formula looks pretty close, but could use a little tweaking. First, are the 1's in your two columns (L and FP) entered as text or as numbers? If they're text, then you've done that part correctly by putting the 1's in quotes. If not, leave the quotes off. Also, the last section also needs to be within parenthesis.

Try this slightly modified formula:

Please Login or Register  to view this content.

If your 1's are text, then try this one instead:

Please Login or Register  to view this content.

[daddylonglegs]

Also, the last section also needs to be within parenthesis.

Not really, this formula should work perfectly well

=SUMPRODUCT(($L$5:$L$1818=1)*($FP$5:$FP$1818=1), EE5:EE1818)

[Richard Schollar]

I must be missing something - why doesn't this work too?:

=SUMPRODUCT(($L$5:$L$1818=1)*($FP$5:$FP$1818=1)*EE5:EE1818)

Richard

[daddylonglegs]

Yes, Richard, it does. I wasn't trying to imply that you needed to use , instead of * to exclude the parentheses.

Of course those two formulas could give you a different result. If there's any text in the range EE5:EE1818 then the latter will fail (i.e. give a #VALUE! error) whereas the first won't.

I'm never quite sure if that's good or bad. You could argue that it's better to get an error to alert you to text in the sum range rather than get an actual figure which might be incorrect because you have text in the sum range......

[Richard Schollar]

Thanks for the clarification

[katie1343]

Thank you so much for the help!