Criteria to display value

[Leo Heuser]

Pat

=IF(C6="apple",C5,"")

 

[Pat]

=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?

 

[Pat]

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

 

[Bob Phillips]

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)

 

[Pat]

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

 

[Bob Phillips]

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)

 

[Leo Heuser]

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.

 

[Bob Phillips]

=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)

 

[Pat]

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

 

[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

 

[Frank Kabel]

Hi
try
IF(c6="apple",c5,"")