Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: selecting data under certain condition (if statements)

  1. #1

    selecting data under certain condition (if statements)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    hello,

    i am trying to select all variables on a sheet that are under this conditions:
    - variables are above a constant(word PICK) and are in the same line as constant(letter: W)
    Book2.xlsx ---- i uploaded my file

    i want to select numbers: 2,25 1,62 but not 3,9 and so on for all data.

    plese help me with this issue.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    What do you mean by "select"? What do you want to do with the information?


  3. #3
    i want to sum them for excemple

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    Does this work for you?

    =SUMPRODUCT(($C$4:$C$326="W")*($G$5:$G$327="PICK"),$G$4:$G$326)

    or on your computer it might be:

    =SUMPRODUCT(($C$4:$C$326="W")*($G$5:$G$327="PICK");$G$4:$G$326)

    this returned sum of 23.97


  5. #5
    thank you so much! problem solved. i just do this for all 3 colums.

  6. #6
    can you please answer another question on this topic.
    i want to write a similar formula as before, but i dont want to sum variables but i want to copy them so i can further analize them. i want this numbers (($C$4:$C$326="W")*($G$5:$G$327="PICK"),$G$4:$G$326) like before but that it paste all the numbers somewhere
    thanks

  7. #7
    maybe some if or lookup function...

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    Try this formula:

    Assuming you are going to start in cell O2:

    =IFERROR(INDEX($G$4:$G$324,SMALL(IF(($C$4:$C$324="W")*($G$5:$G$325="PICK"),ROW($G$4:$G$324)-ROW($G$4)+1),ROWS($O$1:$O1))),"")

    or, again for your probable regional settings...

    =IFERROR(INDEX($G$4:$G$324;SMALL(IF(($C$4:$C$324="W")*($G$5:$G$325="PICK"),ROW($G$4:$G$324)-ROW($G$4)+1);ROWS($O$1:$O1)));"")

    You must confirm this formula by holding the CTRL and SHIFT keys down, then pressing ENTER. You will see { } brackets appear around the formula. Then you can copy down until you get blanks.


  9. #9
    nothing happens if i hold down ctrl and shift. formula returns an error.

  10. #10
    Book2 (10).xlsx i tryed this formula and it returns an error

Page 1 of 2 1 2 LastLast

Posting Permissions

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