How to count reference stored daily to a sheet storing weekly.

I have a sheet that stores records daily (several per day) of installations categorized as either "Res" or "Com".

I'm trying to count the "Res" category above based on week ending dates (Sat) below in "Meters Installs Actuals (RES)".

So for Week Ending 01/14/24 the formula would count all Res from 01/08/24 to 01/14/24. I've tried several variations of the following formula,

=COUNTIFS({Field Production Report 2024 Range Res/Com}, "Res", <=[Week Ending]@row { Field Production Report 2024 Range Install DT }, >([Week Ending]@row - 7))))

Any help would be appreciated! Thank you...

Find more posts tagged with

Formulas

Accepted answers

JamesB

@tgattsh

You are close, just need to define your parameters a little differently.

=COUNTIFS({Field Production Report 2024 Range Res/Com}, "Res", { Field Production Report 2024 Range Install DT },AND(@cell >[Week Ending]@row - 7,@cell<=[Week Ending]@row))

All comments

JamesB

@tgattsh

You are close, just need to define your parameters a little differently.

=COUNTIFS({Field Production Report 2024 Range Res/Com}, "Res", { Field Production Report 2024 Range Install DT },AND(@cell >[Week Ending]@row - 7,@cell<=[Week Ending]@row))

tgattsh

@JamesB It all works as intended! Thank you very much for the help...

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.