Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sumif two condition not works (excel)

    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

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: sumif two condition not works (excel)

    Hi Indra,

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

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumif two condition not works (excel)

    Hi macropod (?)

    It works, big thanks

    best regards,

    Indra

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumif two condition not works (excel)

    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.
    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumif two condition not works (excel)

    Why is a SumProduct formula 'more robust'?
    Microsoft MVP - Excel

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumif two condition not works (excel)

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

  7. #7
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumif two condition not works (excel)

    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.
    Microsoft MVP - Excel

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumif two condition not works (excel)

    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.
    Legare Coleman

  9. #9
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumif two condition not works (excel)

    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.
    Microsoft MVP - Excel

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: sumif two condition not works (excel)

    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 <Enter> 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
    [MS MVP - Word]

  11. #11
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumif two condition not works (excel)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •