Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Chantilly, VA USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I've successfully written a conditional sum array that sums Range 2 IF the value in Range 1 is greater than that in a specific cell, to which I point using "<"&$B60 in this example:

    =SUMIF($A$1:$A$50,"<"&$B60,$C$1:$C$50)

    In this example, $A1:$A50 is Range 1 and $C$1$:$C50 is Range 2.

    This is entered as an array with the curly brackets. All is well.

    I have another set of arrays which tests for two conditions:

    =SUM(IF($A$1:$A$50="Value1",IF($C$1:$C$50="Value2" ,$D$1:$D$50,"")))

    Likewise, all is well.

    But when I try to point back to a cell as I do in the first example, I get a #VALUE! error.

    =SUM(IF($A$1:$A$50,"<"&$B$60,IF($C$1:$C$50="Value1 ,$D$1:$D$50,"")))

    It appears I can make Value 1 a reference with "<"&$B60 in the first example, but not in the second. Does this -- whatever the proper terminology here -- only work when there is one conditional test but not when there are two? Or am I doing something wrong?

    thanks

    Don

  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
    Hi Don,

    Try:
    =SUM(IF($A$1:$A$50="Value1")*($C$1:$C$50="Value2") ,$D$1:$D$50))
    and:
    =SUM(IF($A$1:$A$50<$B$60)*($C$1:$C$50="Value1"),$D $1:$D$50))
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FYI, your first SUMIF formula does not need to be array entered.




    If you are using 2007 or later you can use SUMIFS for multiple conditions, otherwise I'd use SUMPRODUCT (again, no array-entry required):




    =SUMPRODUCT(($A$1:$A$50<$B$60)*($C$1:$C$50="Value1 ),$D$1:$D$50)




    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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