How to count a range of different names from a range of dates?

Sheet A has a week ending date column (always Sunday). Sheet A also has a column called Manpower where I am trying to write a formula. Looks like this,

Sheet B Has an install date and an Installers Name. Looks like this,

The formula I am trying to write would find all dates from Sheet B that fall within the week ending date of Sheet A and count the number of (different installers *8) *5.

Any help from the community would be very appreciated...

Find more posts tagged with

Formulas

Accepted answers

Jason Tarpinian

Looks like you need to wrap your greater than range in () as it's not capturing the -7, so:

=COUNT(DISTINCT(COLLECT({FPR 2024 020124 Range installers}, {FPR 2024 020124 Range Install Date}, <=[2024 Week Ending]@row, {FPR 2024 020124 Range Install Date}, >([2024 Week Ending]@row - 7))))

All comments

Jason Tarpinian

So this formula would count up your distinct number of INSTALLERS based upon some date fields you could reference. So as a breakdown, it is:

collecting all of your INSTALLERS that the INSTALL DT is less than or equal to this Sunday, but greater than last Sunday
The taking the distinct value of installers within that range
Then counting them
So below we collected John, John, Tyler, Caleb. And returned 3, as John is listed twice, but thanks to DISTINCT is only counted once.

=COUNT(DISTINCT(COLLECT(INSTALLER:INSTALLER, [INSTALL DT]:[INSTALL DT], <=[2024 Week Ending]5, [INSTALL DT]:[INSTALL DT], >[2024 Week Ending]4)))

and because I love the extra security (harder for people to delete!) column formulas, this would do the same thing, but as a column formula (with a helper)

=COUNT(DISTINCT(COLLECT(INSTALLER:INSTALLER, [INSTALL DT]:[INSTALL DT], <=[2024 Week Ending]@row, [INSTALL DT]:[INSTALL DT], >IF([Row #]@row = 1, [2024 Week Ending]@row - 7, INDEX([2024 Week Ending]:[2024 Week Ending], MATCH([Row #]@row - 1, [Row #]:[Row #], 0))))))

With helper column of "Row #" calculated as

=MATCH([Row ID]@row, [Row ID]:[Row ID])

where "Row ID" is the system generated column.

tgattsh

@Jason Tarpinian Thank you very much for your reply. Thanks to you I am closer, just not quite there.

This is how I altered your formula to use range references. =COUNT(DISTINCT(COLLECT({FPR 2024 020124 Range installers}, {FPR 2024 020124 Range Install Date}, <=[2024 Week Ending]@row, {FPR 2024 020124 Range Install Date}, >[2024 Week Ending]@row - 7)))

And I used these ranges from another sheet.

It returns a value of 1 when it should return a value of 9. 9 distinct installers were used from 01/01/24 to 01/07/24. Any additional help you would consider offering would be appreciated. Thank you...

Jason Tarpinian

Looks like you need to wrap your greater than range in () as it's not capturing the -7, so:

=COUNT(DISTINCT(COLLECT({FPR 2024 020124 Range installers}, {FPR 2024 020124 Range Install Date}, <=[2024 Week Ending]@row, {FPR 2024 020124 Range Install Date}, >([2024 Week Ending]@row - 7))))

tgattsh

@Jason Tarpinian This worked! Thank you for your 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.