Summary Formula to Show New Value Each Day

Andrea Westrich
Andrea Westrich โœญโœญโœญโœญโœญโœญ

I have a sheet that tracks new employees by type. Within my sheet, I have a date column and employee type columns (example: Intern). I'd like to create a Summary formula within the sheet that will return the number of interns each day. Or a formula that will return the last entry in that column. How can I do that?

Tags:

Best Answer

  • Heather Duff
    Heather Duff โœญโœญโœญโœญโœญโœญ
    Answer โœ“

    @Andrea Westrich

    Ah! This helps give some clarity. This sheet only has interns on it. In this case, let's try this:

    =INDEX([# of interns]:[# of interns], MATCH(TODAY(), Day:Day, 0))


    That should do the trick. Make sure your Day column is set as a date column in column properties. Let me know if it works!

Answers

  • Heather Duff
    Heather Duff โœญโœญโœญโœญโœญโœญ

    Hi @Andrea Westrich

    Try this:

    =countifs([employee type]:[employee type],@cell="Intern",[date]:[date],@cell=today())

    Of course, be sure to replace the bracketed items with your actu

    Let me know if it works!


    Best,

    Heather

  • Andrea Westrich
    Andrea Westrich โœญโœญโœญโœญโœญโœญ
    edited 05/05/21

    @Heather D Thank you! What is the @cell="Intern"? And can @cell be used in a Summary formula?


    I'm getting 0 and today that value is 12. Here is my formula: =COUNTIFS([# of active interns]:[# of active interns], @cell = "# of active interns", Day:Day, @cell = TODAY())

  • Heather Duff
    Heather Duff โœญโœญโœญโœญโœญโœญ

    @Andrea Westrich My apologies - take out the @cell. @cell is what you use when it's a cross-sheet reference.

    =COUNTIFS([# of active interns]:[# of active interns], "intern", Day:Day,TODAY())


    That should help. "intern" is what you're looking for in the column where you display the employee type.

  • Andrea Westrich
    Andrea Westrich โœญโœญโœญโœญโœญโœญ

    @Heather D I still get 0. What is "intern" for? I don't have that any place in my sheet, so I'm not sure I have it right.

  • Heather Duff
    Heather Duff โœญโœญโœญโœญโœญโœญ

    In your example, you were looking for specific employee types, and your example was intern.

  • Andrea Westrich
    Andrea Westrich โœญโœญโœญโœญโœญโœญ

    This is a screenshot of what I use. I want to create a summary field on this sheet that will return the number of interns each day. It should change each day as a new number is entered. This is used as a metric on a dashboard.

    The formula you so kindly provided: =COUNTIFS([# of active interns]:[# of active interns], "intern", Day:Day,TODAY()) gives me a 0. I've taken out the "intern" and it returns incorrect argument set. What am I missing?

  • Heather Duff
    Heather Duff โœญโœญโœญโœญโœญโœญ
    Answer โœ“

    @Andrea Westrich

    Ah! This helps give some clarity. This sheet only has interns on it. In this case, let's try this:

    =INDEX([# of interns]:[# of interns], MATCH(TODAY(), Day:Day, 0))


    That should do the trick. Make sure your Day column is set as a date column in column properties. Let me know if it works!

  • Andrea Westrich
    Andrea Westrich โœญโœญโœญโœญโœญโœญ

    @Heather D you are a genius! With the ",0" I got a no match error, but once I removed that it functions perfectly!!! I can't thank you enough, I've been doing that by hand for a month looking for an answer! I really appreciate it!

  • Heather Duff
    Heather Duff โœญโœญโœญโœญโœญโœญ

    Fantastic! So glad to have helped. :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!