# Thread: selecting data under certain condition (if statements)

1. ## selecting data under certain condition (if statements)

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. What do you mean by "select"? What do you want to do with the information?

3. i want to sum them for excemple

4. 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. thank you so much! problem solved. i just do this for all 3 colums.

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. maybe some if or lookup function...

8. 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. nothing happens if i hold down ctrl and shift. formula returns an error.

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

Page 1 of 2 1 2 Last

#### Posting Permissions

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