Help with a formula

Tzippy

Hi! I'm having trouble figuring out a formula for the following situation.

I have a column called "date applied". I want to know how many candidates applied per day. I also would like to add to that more filters I.E. location. Meaning I want to know how many candidates applied PER DAY PER LOCATION.

Similarly I have a column called "Date scheduled" and another one called "Office Scheduled" - I want to see how many candidates are scheduled PER DAY PER LOCATION.

I hope someone can help me as I've been struggling to figure this out for a while now.

Thanks!

Paul Newcome

Can you provide an example of how you are going to have all of these metrics organized? A screenshot with mock data would help towards finding the most appropriate solution.

Tzippy

For example:

Each row on the smartsheet is another candidate.

I want to add a formula to my metric sheet so I can see the final numbers on my dashboard. The sheet looks like this:

I want to see on the dashboard:

12/21/20 - Bronx - 2

12/21/20 - Brooklyn - 5

12/22/20 - Queens - 3

Paul Newcome

On your metrics sheet, you would be manually entering the date and location. Then you could use a formula along these lines to automate the counts:

=COUNTIFS({Source Sheet Location Column}, [Metrics Sheet Location Column]@row, {Source Sheet Date Column}, [Metrics Sheet Date Column]@row)

Tzippy

oh I see. So it worked but it requires a lot of manual work. Like I have to enter all locations and then update the date for all of them every single day. Is there a better more efficient way to track this in a way where it wont require manual updating daily?

Tzippy

Also, according to your formula, what would be the final formula to have the numbers come up on my dashboard?

Thanks Paul

Paul Newcome

You could enter each location once and then use copy/paste to re-enter them in sections. Then you could manually enter the first date and then copy/paste for the rest of that section.

The formula for the counts would look something along the lines of...

=COUNTIFS({Source Sheet Zone Column}, [Zone Column]@row, [Source Sheet Date Column}, [Date Column]@row)

Tzippy

I'm totally lost now :)

That formula should pull info from the source sheet and not the metric sheet?

Paul Newcome

Ok. So you set up your metrics sheet with the date(s) and location(s).

Then you use a formula in the metrics sheet similar to the one above with cross sheet references to look at the source data and compare it to the data that is on that particular row of your metrics sheet to pull the count.

If your locations are always going to be the same for each date, then there may be a way to automate the dates depending on your exact needs. Are you looking for a rolling date or are you looking for a continuous series of dates?

Tzippy

Would I be able to call you so you can guide me over the phone?

Paul Newcome

How do you plan on setting up your metrics sheet?

Tzippy

I followed your formula instructions.

I added one column that has all locations listed and another column with sample dates of today, tomorrow and created the following formula:

=COUNTIFS({Source Sheet Location Column}, [Metrics Sheet Location Column]@row, {Source Sheet Date Column}, [Metrics Sheet Date Column]@row)

It works but its not efficient because I will have to update the date regularly. Now my question is how to view the total numbers on the dashboard.

Paul Newcome

What do you mean you have to update the dates regularly? How often and what updates are you going to need to make? Will it always be "Today" and "Tomorrow"? If so, we can create a formula that will automatically update these dates for you.

Tzippy

No - its an ongoing report. I want to be able to see how many apps come in every day per location.

Paul Newcome

How long of a running total would you want? Would you want to track from today through the past 2 weeks or 6 months, or some other time period so that it is always looking back x number of days from today, or are you wanting to track from today all the way back to the beginning even if the beginning is 1,000 days ago?

Tzippy

I think up to two weeks in the past including 'today' would be a good start.