Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: ???? on sumif?

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    100
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    a b

    1 ab 20
    2 xy 10
    3 kr 15

    I want to add the values of b ONly if the values in a = xy or kr. The result should be 35.

    Help!

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,487
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default



    two ways follow:

    =SUMPRODUCT((A1:A3={"Kr","xy"})*(B1:B3))

    or

    =SUMIF(A1:A3,"xy",B1:B3)+SUMIF(A1:A3,"kr",B1:B3)

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,312
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is this ok?

    =sumif($A$1:$A$100,"xy",$B$1:$B$100)+sumif($A$1:$A$100,"kr",$B$1:$B$100)

    Hope that helps, -someone may post a shorter formula

    Adam

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,024
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default


    Try:

    =SUMIF(A2:A4,"xy",B2:B4)+SUMIF(A2:A4,"kr",B2:B4)

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,312
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    case in point,

    good call on sumproduct, Dave.

  6. #6
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Try,

    =SUMIF(A1:A3,"ab",B1:B3)+SUMIF(A1:A3,"kr",B1:B3)

    or

    =SUMPRODUCT(((A1:A3="ab")+(A1:A3="kr"))*(B1:B3))

    Bye,
    Jay

  7. #7
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-24 10:17, Dave Patton wrote:
    =SUMPRODUCT((A1:A3={"Kr","xy"})*(B1:B3))
    Nice one!

  8. #8
    Board Regular
    Join Date
    Apr 2002
    Posts
    100
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    tHANKS!!!!!!!

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,024
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    I'd suggest using the SUMIF version, which allows you to use the cell refs instead of "kr", etc. That is, you can put e.g., "xr" in E1 and "kr" in E2 and use:

    =SUMIF(A2:A4,E1,B2:B4)+SUMIF(A2:A4,E2,B2:B4)

    If the OR criteria/conditions count 3 or more, you might want to consider using:

    =SUMPRODUCT((ISNUMBER(MATCH(A2:A100,E1:E3,0)))*(B2:B100))

    where E1:E3 houses the OR criteria.

    Aladin

  10. #10
    Board Regular
    Join Date
    Apr 2002
    Posts
    100
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OH, AND thanks for not pointing out or making fun of the fact that I don't know how to add!

    dawn

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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