IF AND Function For Reporting

Marcus
Marcus ✭✭
edited 12/09/19 in Formulas and Functions

Hi guys,

I am trying setting up a new report for my team which will allow them to bring through project activities based on the following criteria:

  • The week the activity happened in is before today's current week.
  • The RYG status is red OR green OR yellow

I am using a column set up as a checkbox and trying to put an IF( formula behind that to trigger a tick if the above criteria are present. 

=IF(AND(WEEKNUMBER(Finish183) < WEEKNUMBER(TODAY())), 1)

This formula seems to work to tell me if the activity happened before the week I am in, however I am having difficulty adding the second criteria on to this.

 

Does anyone know how the formula should be formatted to ask the RYG status as a second criteria, but also only if it is red or green or yellow (don't want to report on blue)

 

Appreciate any help smiley

 

Kind regards

 

 

Tags:
· Share on FacebookShare on Twitter

Comments

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi Marcus,

    If you're creating a report, you can actually have RYG criteria built into the Report Builder, meaning that you don't have to create a formula in the source sheet.  



    To do so, create a new report and select your source sheet. Go to the "What?" section and choose the column that contains your RYGB status balls. Then in the next window, select the checkbox that indicates a colour should be excluded and choose to exclude Blue.

    In my screen capture below, "Health" is the name of the RYGB column in my source sheet and I have decided to exclude any rows with the Gray ball from the report. 

    Cheers!

    Genevieve

    Screen Shot 2019-11-18 at 1.12.58 PM.png

    · Share on FacebookShare on Twitter
  • Marcus
    Marcus ✭✭

    Hi Genevieve,

    Thanks for responding. That would normally work but I may have missed a piece of information out from my original post. I have a separate column for checkboxing any finish date that is the same weeknumber as today's weeknumber. 

    The two formulas I'm using in the report are:

    =IF(WEEKNUMBER(Finish24) = WEEKNUMBER(TODAY()), 1)

    =IF(WEEKNUMBER(Finish24) < WEEKNUMBER(TODAY()), 1)

    I only want the RYG to be limited on the second formula. If I try to add RYG as a 'what' property in the report it won't work as I have the report set up as an or options as per attached screenshot.

    I want the report to be able to bring through any activity which happened in this week (regardless of RYG) but also any activity which happened in any week prior to this week (but I don't want to show blue a.k.a complete tasks).

     

    Does this make a bit more sense?

     

    Cheers

     

    ryg.PNG

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    Hi Marcus,

    Thank you for the clarification! Would adding an "AND" function to your formula help?

     

    =IF(AND(WEEKNUMBER(Finish24) < WEEKNUMBER(TODAY()), [RAG Status]@row<> "Blue"), 1)

    · Share on FacebookShare on Twitter
  • Marcus
    Marcus ✭✭

    That's sorted it!!

     

    Thank you very much for your help

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!