SUMIF with 2 conditions.

**Martinac** · 12-02-2007, 09:19 PM

Hi,

I need to sum the column "M" with these conditions: L:L>=-0.2 and M:M>=0.1.

In other words, I need to merge these formulae:
=SUMIF(L:L;">=-0.2";M:M)
=SUMIF(M:M;">=0.1")

I've tried all methods recommended, but to no avail. Does anybody have a clue?
I'm using a Czech version of Excel 2003.

**Mikeopolo** · 12-02-2007, 11:55 PM

In a single cell, try:

=sumproduct((M1:M65536>=0.1)*(L1:L65536>=-0.2)*(M1:M65536))

not tested
Sumproduct cannot refer to an entire column

hth
Mike

**Martinac** · 12-03-2007, 12:57 PM

Unfortunatelly, the formula returns the #NUM! error.

But I figured out it should be something like this (ah):

{=SUM(IF($L$6:$L$1000>=-0.2;IF($M$6:$M$1000>=0.1;$M$6:$M$1000;0);0))}

And since it's array formula, it's necessary to press control+shift+enter when finished with writing.

**NBVC** · 12-03-2007, 01:01 PM

=SUMPRODUCT(($L$6:$L$1000>=-0.2)*($M$6:$M$1000>=0.1)*($M$6:$M$1000))

should work also.

The reason you got #NUM error is that Sumproduct cannot refer to the entire column....so you can't use A:A nor can you use A1:A65536...but you can say A2:A65536 or A1:A65535

**Martinac** · 12-04-2007, 01:54 PM

The main problem with sumproduct is that it's not supported in my Czech version of Excel, so I got #NAME? error this time

. The function probably has a different name or something.

Nevertheless, the formula with nested functions is working fine.

Thank you.

**Mikeopolo** · 12-04-2007, 03:02 PM

@nbvc - thanks for your correction, will take on board!

@martinac - pity it's not available, as it's very useful and not even an array formula. Ah well...

Regards
Mike