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

**Bjornar** · 12-04-2007, 03:30 PM

I think you Czech version supports SUMPRODUCT. The problem is probably that it has local name. Open my attachment and look in cell A1, I put a SUMPRODUCT formula there. Your Excel version will translate this for you.

The best thing for you people that uses local language versions of Excel too do, for getting support, is to attach your sheet. Then the people helping you can edit it in their local language (English for most people here), and it will translate back when you open it.

As a side note: Is there anybody out there that support Microsofts ide of using local language names for the formulas ?? Excel is one of the best and most flexible software ever made. Why have this stupid idea ? I live in Norway and started to use Lotus 1-2-3 over 20 years ago, and later changed to Excel. I have a English version on my computer, and gets very, very confused if I have to work on a Norwegian version. Did someone in Microsoft realy think that because I speak fluent Norwegian it would be better for me with Norwegian names on the formulas ? Everybody has to learn the formulas name anyhow, so why not learn an international name ?

**Martinac** · 12-15-2007, 05:03 AM

Sorry to reply so late.

Since I'm using both Czech and English versions, I struggle with the different function names all the time. I think there's no point in having it.

I'd attach my file here, but it's about 2,5 MB and maximal file size for upload is set to 100KB. But it's ok now as I resolved the problem with the array formula workaround.

Do you, or anybody, know how to make my local version of Excel use the English formulas?

BTW
Excel 2007 offers conditional sum with multiple conditions. It's called SUMIFS:
SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)
I think they should have included it a long time ago.