+ Reply to Thread
Results 1 to 5 of 5

Can you use SUMIF for 2-columns of criteria ?

  1. #1
    Registered User
    Join Date
    11-07-2003
    Posts
    26

    Can you use SUMIF for 2-columns of criteria ?

    Using 2-criterias, how do I extract a column value from a 3rd

    column?

    On sheet_#1 I am trying to obtain a value from sheet_#2 where

    the value of interest is in the 3rd column, and only extract

    the value if the 1st and 2nd column meet specific criteria

    (example shown below).

    I have tried using SUMIF but I am unsuccessful in developing

    the formula for the 2nd critera, can someone assist ?

    example of intent of logic in a cell in sheet_#1
    =sumif(A1:C10,("BLue" AND "Low"),C1:c10)

    sheet_#2 columns

    "A "B" "C"

    row1 Blue High 2
    row2 Blue Low 12
    row3 Blue Mid 6
    row4 Blue Closed 7
    ,,,
    ,,,


    Thanks in advance for you help

    jay

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Try something like

    =sumproduct(--(a1:a10="Blue),--(b1:b10="High"),(c1:c10))


    rylo

  3. #3
    Registered User
    Join Date
    11-07-2003
    Posts
    26

    Your example works ! ... But what do the double 'dash' mean

    Hi Rylo,

    Your a genius, and "Thank you" very much. Your SUMPRODUCT example does the task, however, I am not fimiliar with what the double dashes in the formula do.

    Could you educate me ?

    sumproduct(dash dash(a1:a10="Blue),dash dash(b1:b10="High"),(c1:c10))

    from your

    sumproduct(--(a1:a10="Blue),--(b1:b10="High"),(c1:c10))

    Thanks again

    jay

  4. #4
    Registered User
    Join Date
    07-16-2007
    Posts
    43

    If I may jump in...

    The double dashes are two negative signs.

    (a1:a10="Blue") results in an array of TRUE or FALSE values, and you need to convert that to an array of 0s and 1s to be able to multiply it with your array of values (the values in your third column). Sumproduct multiplies the elements of the arrays you put into it and sums the result.

    You can convert TRUE or FALSE to 0s and 1s with various methods, the double negative sign is one of them. If you only used one negative sign, you would have an array of 0s and -1s.

    Brigitte

  5. #5
    Registered User
    Join Date
    11-07-2003
    Posts
    26

    I'm wow'ed agian :-))

    Thank you Brigitte (and again Rylo) for providing some new insight. Its great to learn something new everyday :-)

    Jay

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1