COUNTIFS the referenced sheet have the value that is equal or less than a cell in the sheet

Options

Hello, I'm trying to make a formula that counts from year to date, but I just want it to count from the month the sheet is refering that is in a cell. I tried this, but didn't work.

COUNTIFS({Referenced Sheet 1}, "", {Referenced Sheet 2}, ="Element", {Referenced sheet 3}, <=@cell)

Thanks

Answers

  • J Tech
    J Tech ✭✭✭✭✭
    Options

    Hi @EmilyEchevarria,

    It looks like you are trying to use the <= operator with a cell reference to dynamically set the end date for your COUNTIFS formula. To reference the month of the sheet, you can use the MONTH function to extract the month number from the cell reference and then construct a new date using the DATE function with the current year and the extracted month.


    Here's an example formula that should work:

    =COUNTIFS({Referenced Sheet 1}, "", {Referenced Sheet 2}, "Element", {Referenced sheet 3}, ">=" & DATE(YEAR(TODAY()), MONTH(@cell), 1), {Referenced sheet 3}, "<=" & DATE(YEAR(TODAY()), MONTH(@cell) + 1, 1) - 1)

    In this formula, we're using the TODAY() function to get the current date and the YEAR and MONTH functions to extract the year and month from the cell reference. We're then using the DATE function to construct the start and end dates for our COUNTIFS formula, and concatenating them with the operators >= and <=, respectively.


    Make sure to replace the cell reference @cell with the cell reference to the cell containing the month you want to count from.

    Regards

    J Tech

    If my response has helped you in any way or provided a solution to your problem, please consider supporting the community by marking it as Insightful, Vote Up, or Awesome. Additionally, you can mark it as the accepted answer, which will make it easier for others to find a solution or get help with a similar issue in the future. Your support is greatly appreciated!
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @J Tech

    I hope you're well and safe!

    That formula won't work because it's for Excel and not Smartsheet. For example, you can't use the & in a Smartsheet formula like yours.

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • J Tech
    J Tech ✭✭✭✭✭
    Options

    Please try


    =COUNTIFS({Referenced Sheet 1}, "", {Referenced Sheet 2}, "Element", {Referenced sheet 3}, >=DATE(YEAR(TODAY()), MONTH(@cell), 1), {Referenced sheet 3}, <=DATE(YEAR(TODAY()), MONTH(@cell) + 1, 1) - 1)

    Regards

    J Tech

    If my response has helped you in any way or provided a solution to your problem, please consider supporting the community by marking it as Insightful, Vote Up, or Awesome. Additionally, you can mark it as the accepted answer, which will make it easier for others to find a solution or get help with a similar issue in the future. Your support is greatly appreciated!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!