Formula to Count Observations by Day, Week, and Year from Another Sheet

System

This discussion was created from comments split from: CountIf with Date range.

JORGE GONZALEZ

Hello, could you help me.
I want to make a formula that allows me to count the number of observations that a person made during a day, a week and a year.
This information comes from another smarsheet datasheet.

Archiduc

What are not you managing to do? bring the data from the other sheet? manage the date formula?

Use a countifs formula and click on "reference another sheet" after opening the first parenthesis of the formula. Find the sheet your data is in, click the range you need to count, then finish your formulas with the criteria(s). For the date I'm not very good but there are many example of such a formula in the community if you search a bit. Search for "countif with date"

Kelly Moore

Hello @JORGE GONZALEZ

Without screenshots of the sheets, I will assume you have the names of the relevant people, each name on a separate row, on your summary sheet- which is where the formulas will exist. You will need a day column, week column and year column on this sheet. The calculation values are transient - that is, the values will change each day, week, etc. If you need to keep records of the daily data then I would build a third sheet and use it as a run-log sheet. Each day, at say 10pm, I would COPY the formula sheet data (using COPY row automation) to the run-log sheet to preserve the data.

Day Data

=COUNTIFS({Source sheet Name column}, Name@row, {Source sheet Date column}, AND(ISDATE(@cell), @cell=TODAY()))

Week Data

=COUNTIFS({Source sheet Name column}, Name@row, {Source sheet Date column}, AND(ISDATE(@cell), WEEKNUMBER(@cell)=WEEKNUMBER(TODAY()), YEAR(@cell)=YEAR(TODAY())))

Year Data

=COUNTIFS({Source sheet Name column}, Name@row, {Source sheet Date column}, AND(ISDATE(@cell), YEAR(@cell)=YEAR(TODAY())))

As an alternative solution, have you considered building three reports - Daily, Weekly and Yearly, to accomplish your summary? It would take 3 helper columns on your source sheet but could display the data on a dashboard. If you are interested in this solution, I can walk you through it.

Kelly