selecting data under certain condition (if statements)

K

kronikjb

New member
Joined
Jan 30, 2015
Messages
14
Reaction score
0
Points
0
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)
View attachment 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.
 
What do you mean by "select"? What do you want to do with the information?
 
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
 
thank you so much! problem solved. i just do this for all 3 colums.
 
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
 
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.
 
nothing happens if i hold down ctrl and shift. formula returns an error.
 
While you are holding them down press ENTER.

See attachment.
 

Attachments

  • Book2.xlsx
    28.1 KB · Views: 1
problem solved, thank you it was regional settings... i changed the comma before "row" to ; and it worked
=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 log in or register to reply here.
Back
Top