Summary Formula to Show New Value Each Day

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?

Heather Duff

@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!

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

@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

@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

@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

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

Andrea Westrich

Interns.jpg

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

@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

@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

Fantastic! So glad to have helped. :)