sumif two condition not works (excel)

Topic

New Reply

I have sumif with two condition in worksheet ‘budget monitoring’ in highlight column which doesn’t work. Is there anyone can help?

I was preparing template sheet for project in several remote area with sumif with two condition requires array formula, I find it is not stable, everytime someone check the formula by clicking F2, the array disappears, and the function/formula result was incorrect. Is there any solution for this?

best regards,

Indra

Reply | Quote

Replies

Hi Indra,

Here’s something more robust, using SUMPRODUCT instead of an array formula.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi macropod (?)

It works, big thanks

best regards,

Indra

Reply | Quote

In addition to using SUMPRODUCT, you might also want to consider protecting the worksheet (Tools/Protection/Protect Sheet) with all of the cells containing formulas locked (Format /Cells/Protection Tab) so that the users can not accidentally change the formulas.

Reply | Quote

Why is a SumProduct formula ‘more robust’?

Reply | Quote

Becuse selecting the cell, pressing F2 then enter does not break it.

Reply | Quote

Suspected that much. But, that notion of robustness is unfortunate for it qualifies all multicondinoal formulae involving MAX, MIN, AVERAGE, MEDIAN, etc. as non-robust.

Reply | Quote

I don’t think it has anything to do with multicontidional functions. It has to do with array formula (with or without multicontidional functions) becomming non-array formula if Ctrl/Alt are not held down when Enter is pressed.

Reply | Quote

Calling

=SUMPRODUCT((X=x)*(Y=y),Z)

‘more robust’ compared with:

{=SUM(IF(X=x,IF(Y=y,Z)))}

tends to have an unfortunate implication that:

{=MAX(IF(X=x,IF(Y=y,Z)))}

{=AVERAGE(IF(X=x,IF(Y=y,Z)))}

and similar multiconditional formulae are ‘less robust’, ‘non-robust’, etc.

Reply | Quote

Hi Aladin,

I was using the term ‘robust’ only in the sense that the formula doesn’t fall over when some klutz selects the cell and presses the key. This was the problem Indro was wrestling with.

No implication as to the reliability of array formulae per se was meant – I use array formulae extensively and find them quite useful. When the worksheet are used by others, though, one has to consider the risk that they might inadvertantly kill an array formula. I’ve seen this happen any number of times.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

previously I captured array formula of sumif from Aladin in other site, which was useful also for my knowledge. but in this case, I only concern not using array formula only for sumproduct exactly maropod said.

My great appreciation for you all.

Indra

Reply | Quote