Countifs with distinct on four column from another worksheet

Hello Everone,

I tried to post this before, but it was probably the wrong way.

I am new on the community, though I have been using Smartsheet for few years, (I am French, maybe my English will be different than yours sorry about that).

I came here to look for an answer to my problem. I am trying to use the Distinct function combine with countifs, but I can't get it to work

.Here is what I am trying to do.

I collect a report of my workers days from a form on a sheet with the names, date and all other type of info I need from the job site.

I am trying to extract from that sheet how many differents days the man have worked on a month, knowing that I can have 2 or 3 input for a same day (morning on one job and afternoon on another one)

=COUNTIFS({Repports JOBSITE 2021 Names}; =Name@row; {Repports JOBSITE 2021 Month}; =Month@row; {Repports JOBSITE 2021 Year}; =Year@row)

This formula give me the count of input by worker but it doesn't give the exact number of workday.

I am not sure were to use the DISTINCT function

I can provide you with more info if you need to understand better my problem.

I would very much appreciate any input that can help me go forward with this.

I could use this type of function for many of my Smartsheet tables.

Thank you for taking the time to read.

Olivier

ABC Construction

Find more posts tagged with

Accepted answers

Paul Newcome

I would suggest something along the lines of...

=COUNT(DISTINCT(COLLECT({Repports JOBSITE 2021 Date Column}; {Repports JOBSITE 2021 Names}; =Name@row; {Repports JOBSITE 2021 Month}; =Month@row; {Repports JOBSITE 2021 Year}; =Year@row)))

All comments

Andrée Starå

Hi @Olivier LEMBERT

I hope you're well and safe!

Welcome to the Community and the wonderful world of Smartsheet!

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

I would suggest something along the lines of...

=COUNT(DISTINCT(COLLECT({Repports JOBSITE 2021 Date Column}; {Repports JOBSITE 2021 Names}; =Name@row; {Repports JOBSITE 2021 Month}; =Month@row; {Repports JOBSITE 2021 Year}; =Year@row)))

Olivier LEMBERT

@Paul Newcome

https://community.smartsheet.com/discussion/comment/300219#Comment_300219

Thank you it work perfect, I had tried something like that but I had the wrong order of things.

Best regards France

Olivier

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.