Using IF/THEN for my formula

sophiaashepard

Hello,

I recently received advice on here to develop a formula that lists out all projects that individuals are billing to via cross sheet formulas. The below formula would go into the Projects column in the Enterprise Rollup.

=JOIN(COLLECT({Project CHEAT}, {Person}, Person@row), ", ")

This formula works, however, it is adding projects that are marked as "Inactive". I would like to update the formula to only calculate projects that are marked as "Active" in the 'Active ProjectCHEAT' column. I tried this formula but I get a #UNPARESABLE response:

=if({Active Project CHEAT}="Active", JOIN(COLLECT({Project CHEAT}, {Person}, Person@row), ", "),"")

Data Input sheet:

Enterprise Rollup:

Jason Tarpinian

COLLECT() can use as many criterion as you need, so just put the logic in that function instead:

=JOIN(COLLECT({Project CHEAT}, {Person}, Person@row, {Active Project CHEAT}, "Active"), ", ")

sophiaashepard

Thank you @Jason Tarpinian ! What if I am trying to have the active be mulitple values… looking do the same thing for listing proposals but want it to show up if the status is Won, Assume Win, or Assume Lose. This is what I tried but did not work:

=JOIN(COLLECT({Proposal CHEAT}, {Data Input Proposal Person}, Person@row, {Proposal Status}, ="Won", "Assume Lose" ,"Assume Win"), ", "))

Jason Tarpinian

You can use an OR statement within the criterion. The caveat to this is that when you are checking criterion against an entire range, you have to specify to look at each individual cell using the @cell reference.

=JOIN(COLLECT({Proposal CHEAT}, {Data Input Proposal Person}, Person@row, {Proposal Status}, OR(@cell = "Won", @cell = "Assume Lose", @cell = "Assume Win")), ", ")