IF Countif and Textjoin - Help required

[satish gubbi]

Hi team,

I have created the attached temaplate, I would require Textjoin formulae to execute only if corresponding cell has got Increase or Decrease. I have used If>Countif and Textjoin but not getting the result as expected.

Request your help in getting this resolved.
30236

[p45cal]

Are you looking for:
In D2:

=TEXTJOIN(" ",TRUE,FILTER(B2:B16,A2:A16=D1,"No Increase"))

and in K2:

=TEXTJOIN(" ",TRUE,FILTER(B2:B16,A2:A16=K1,"No Decrease"))?

[satish gubbi]

Hi @P45cal

Thank you so much for your help, this formula working as intended.

Thank you so much once again. Have a wonderful weeks......


Are you looking for:
In D2:

=TEXTJOIN(" ",TRUE,FILTER(B2:B16,A2:A16=D1,"No Increase"))

and in K2:

=TEXTJOIN(" ",TRUE,FILTER(B2:B16,A2:A16=K1,"No Decrease"))?

[satish gubbi]

Hi P45cal,

This formula works only in Office 365 or Office 2022. Any alternative for FILTER formula for lower version Excel.

Please advise. Thanks in advance

[p45cal]

You have TEXTJOIN but not FILTER?
Try:

=TEXTJOIN(" ",TRUE,IF(A2:A16=D1,B2:B16,""))
and

=TEXTJOIN(" ",TRUE,IF(A2:A16=K1,B2:B16,""))

[satish gubbi]

Hi P45cal,

Yes, we have TEXTJOIN but not FILTER, the revised formula working fine as intended

Thank you.


You have TEXTJOIN but not FILTER?
Try:

=TEXTJOIN(" ",TRUE,IF(A2:A16=D1,B2:B16,""))
and

=TEXTJOIN(" ",TRUE,IF(A2:A16=K1,B2:B16,""))

[satish gubbi]

Hi P45cal,

Can we have multiple criteria to join the text, I have attached file here with the example and explanation.

Request your help in resolving this.

[arnelgp]

can you use And() in your IF() expression?

[satish gubbi]

can you use And() in your IF() expression?

I did use folliwng formula
but not fetching data a expected.


=TEXTJOIN(", ",TRUE,IF(AND(A2:A16="Decrease",B2:B16="Service"),C2:C16," "))

[Fluff]

You need to use the array form of IF
=TEXTJOIN(", ",TRUE,IF((A2:A16="Decrease")*(B2:B16="Service"),C2:C16,""))

[Aussiebear]

Should we not be determining the criteria to see if its suitable before deciding to TextJoin?
In Cell D1


=If(And(A2:A16 ="Increase",B2:B16 ="Service",TextJoin(" ",True)," "))

In Cell K1


=If(And(A2:A16 ="Decrease",B2:B16 = "Service",TextJoin(" ",True)," "))

[satish gubbi]

You need to use the array form of IF
=TEXTJOIN(", ",TRUE,IF((A2:A16="Decrease")*(B2:B16="Service"),C2:C16,""))

@Fluff,
This is working as intended. thank you so much

[satish gubbi]

Should we not be determining the criteria to see if its suitable before deciding to TextJoin?
In Cell D1


=If(And(A2:A16 ="Increase",B2:B16 ="Service",TextJoin(" ",True)," "))

In Cell K1


=If(And(A2:A16 ="Decrease",B2:B16 = "Service",TextJoin(" ",True)," "))


Hi Aussiebear,
Thanks for your reply:
I did modify your formula to fetch data from C column but not getting the output.


=IF(AND(A2:A16 ="Increase",B2:B16 ="Service"),TEXTJOIN(" ",TRUE,C2:C16)," " )

[Aussiebear]

Fair enough. Fluff looks as if he's nailed it. Shame he's not here more often.

[Fluff]

@Fluff,
This is working as intended. thank you so much Glad to help & thanks for the feedback.

Shame he's not here more often.Normally when I look on here everything is dealt with.