Criteria to display value

  • Thread starter Thread starter Leo Heuser
  • Start date Start date
=c5

value returned

=c5 if c6 text = "apple"

c5 value returned

=c5 if c6 text = "" any other text

c5 value not returned

What I need is a formula where only the value in c5 will return where the
text in c6 equals "apple"

Anyone know the formula for this?
 
Thank you Leo that worked nicely.

I want now to incorporate the following formula, this one adds each of the
values of each cell.

=IF(CT89="apples",CX89+CZ89+DF89+DH89+DN89+DP89,"")

into the formula you provided, I had a go myself but its not working
properly. This one should display each value seperated by a comma


=IF(CT89="apples",SUBSTITUTE(TRIM(CW89&" "&CY89&" "&DF89&" "&DH89&" "&DO89&"
"&DQ89)," ",","))

Regards
Pat
 
Pat,

Do you mean

=IF(CT89="apples",CX89&","&CZ89&","&DF89&","& DH89&","& DN89&","&DP89,"")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob,

Yes that is almost what I am looking for, only if there is no value to
return for any of the cell a comma will display anyway. For example

=IF(CT89="apples",CX89&","&CZ89&","&DF89&","& DH89&","& DN89&","&DP89,"")

could return

C1,,,G4,H7,,

this is what should have been returned

C1,G4,H7

Where a value is not returned for any cell the comma separator should not
display.

Pat
 
Pat,

Here is a partial solution , but it only handles 2 missing values

=SUBSTITUTE(SUBSTITUTE(IF(CT89="apples",CX89&","&CZ89&","&DF89&","&
DH89&","& DN89&","&DP89,""),",,",","),",,",",")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
You're welcome, Pat.

For your new problem try:

=IF(CT89="apples",TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(" "&CX89&","&CZ89&","&DF89&","& DH89&
","& DN89&","&DP89&" ",",,,,",","),",,,",","),",,",",")," ,",""),",
","")),"")

Please notice, that I have added a space " " at each end of
your original string to catch a starting or trailing comma.
 
=IF(CT99="apples",SUBSTITUTE(TRIM(CW99&" "&CY99),"
",","),"")&","&IF(DC99="apples",SUBSTITUTE(TRIM(DF99&" "&DH99),"
",","),"")&","&IF(DL99="apples",SUBSTITUTE(TRIM(DO99&" "&DQ99)," ",","),"")


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thank you everyone for your help. I have been playing around with the
various formulas and have found the following works almost to what I am
looking ultimately to do:

=IF(CT99="apples",SUBSTITUTE(TRIM(CW99&" "&CY99),"
",","))&","&IF(DC99="apples",SUBSTITUTE(TRIM(DF99&" "&DH99),"
",","))&","&IF(DL99="apples",SUBSTITUTE(TRIM(DO99&" "&DQ99)," ",","))

This works if apples is selected in either CT99, DC99 or DL99 . If either
one or all has a different value other than apples it returns FALSE.
Does anyone know what is needed so FALSE does not display?

Pat
 
That sorts that one out!
Just to kept things nice and tidy, where I use the second and third formula
with

&","&IF

this creates commas regardless if there is no values to return for some part
of the formula.

What would solve that problem?

Pat
 
Back
Top