SumIf with multiple criteria?
SumIf with multiple criteria?
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: SumIf with multiple criteria?

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I want to evaluate a boolean value in one column (B2:B1000), then, if the value is true, evaluate column C (C2:C1000) and if that value is also true, sum D (D2:D1000).

    I want to avoid using a conditional as I am using this sheet on my IPaq as well as my PC and you can't use conditionals on a CE device.

    TIA

  2. #2
    Guest

    Default

    I think this might help you out hopefully

    =SUM(((b2:b1000=XXX)*(C2:c1000=xxx))*(d2:d2000))

    as an array formula (shift alt delete)

    fill in the xxx to whatever value or cell reference you want

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,803
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-03 18:09, mdecourcy wrote:
    I want to evaluate a boolean value in one column (B2:B1000), then, if the value is true, evaluate column C (C2:C1000) and if that value is also true, sum D (D2:D1000).

    I want to avoid using a conditional as I am using this sheet on my IPaq as well as my PC and you can't use conditionals on a CE device.

    TIA
    In E2 enter: =(B2<$F$1)*(C2=$F$2)*D2

    where F1 and F2 houses your conditions.

    Adjust the comp operators < and = to suit and copy down this as far as needed.

    In F2 enter: =SUM(E:E)

    Would this work on CE?

    Aladin

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    My mistake, I meant to say I can't use an array formula as THEY don't work on CE.

    I have one cell I want to return the total to so if I had a single column to evalute I would have; =sumif(B:B, "y", C:C).

    But I want to evalute A based on the results of B, so if B:B is "y" AND A:A is "y" then Sum C:C.

    Hope this is clear.

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,803
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    On 2002-03-04 07:43, mdecourcy wrote:
    My mistake, I meant to say I can't use an array formula as THEY don't work on CE.

    I have one cell I want to return the total to so if I had a single column to evalute I would have; =sumif(B:B, "y", C:C).

    But I want to evalute A based on the results of B, so if B:B is "y" AND A:A is "y" then Sum C:C.

    Hope this is clear.
    Try what I proposed. It is not array formula. With your now more specific info, do:

    In E2 enter: =(A2="y")*(B2="y")*C2

    where I assumed the data to start in row 2, otherwise adjust to suit, and copy down this as far as needed. Then use

    =SUM(C:C)

    to obtain the desired total.

    Aladin

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
  •  

 

 
DMCA.com