I am trying to count documents and save a snapshot for a specific month

I am counting documents in one column "SHEQ Document Index Annual Review Range 2" with a specific dropdown "[Document Status]@row" for a specific month and year. However in the following month when the "[Document Status]@row" changes status for that same document, I do not want the Count for the previous month to change, it needs to be static. The below is the formula I used. I tried using the IF Statement, but could not succeed

=(COUNTIFS({SHEQ Document Index Annual Review Range 2}, [Document Status]@row, {SHEQ Document Index Annual Review Range 3}, "2024", {SHEQ Document Index Annual Review Range 5}, "2"))

Find more posts tagged with

Accepted answers

All comments

AravindGP

Hi @Romar

I assume you already have columns capturing the month and year of the status changes. If you add that to your collect statement as an additional parameter, you should be able to get the changes made by month and year. For the closing, if you anticipate no changes at the end of the month for a row, you can look up the month and year from the modified date column (add it in, if you haven't already). I can share a formula if you can add some source data for the reference columns.

Romar

Romar

I have pasted the source data above

Genevieve P.

Hi @Romar

Can you clarify, are you adjusting the same rows in the source sheet with new data, but you want the old formulas to stay static in your metric sheet?

Formulas can only look at the current data that's being displayed on the other sheet - there isn't a way to use a formula to look at the cell history and stay static.

If you're on an enterprise plan, you could potentially use Work Insights on the side panel to see data across time, here's more information: Use Work Insights to visualize your data

Cheers,

Genevieve

Romar

Can you clarify, are you adjusting the same rows in the source sheet with new data (drop down), but you want the old formulas to stay static in your metric sheet? "Yes", however for the previous month and not the same month which the row is been changed for.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.