Using Countifs in a cross reference formula

Hello - This is the formula I am using: =COUNTIFS({Company RAID Log Range 1}, "XXXXXX", {Company RAID Log Range 2}, "CVI")

Company RAID Log Range 1 = LEAD Column

Company RAID Log Range 2 = Ownership Column

where XXXXXX is a person's name. This is what I hope is returned: count of times this persons name is list in the LEAD column AND the OWNERSHIP TEAM = CVI.

This information is in the same Worksheet. I know the person is named the lead 45 times. When I use this formula the counts returned are wrong. For instance - for Ownership Team = FINANCE =43 but my formula is saying 27. When I ask for the count of ownership team = CVI my formula returns 5 but person XXXXXX is only the LEAD for 2 of them.

Person XXXXXX is not responsible for any activities where the ownership Tema = supply chain but my formula brings back an answer of 3.

What I am attempting to count is this: In the RAID LOG for person XXXXXX how many activities are they responsible for by Ownership Team? So I want to use my 1st range to limit to the person and the second range to return the count

Do I need an AND? or is my formula backward? Different Formula?

Thanks in advance for your help!!

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

There is nothing wrong with your formula syntax. Double check that you have selected the correct reference sheet as well as the correct columns for each of your ranges.

If that is all set up properly, try applying a filter to the source sheet that mimics your COUNTIFS range/criteria sets.

L_Christine

Hello and Thank you for confirming my formula was correct.

I have another question I would like to learn.

We have these RAID Log items assigned to several people. I would like to show progress being made for RAID log items that are modified between Monday-Wednesday of a week and Thursday-Sunday. The assumption is that if a RAID Log Item is changed by the owner then they are recording progress for the item.

There is a modified column that indicated the date the row was last changed and there is a column that indicates who made the change.

I would like the formula to count the # of RAID Log ITEMS (ROWS) Assigned to a specific lead that are not "closed" (status column) that were updated between Monday and Wednesday of every week or Thursday - Sunday of every week.

My example person may have 45 RAID items - 15 are closed so remove them meaning there are 30 that can be updated in that week some will be update more than once, some will be updated in the 1st or second half of the week; etc.

Smartsheet knowledge experts - how would you do this.

Best Regards,

LChristine

Paul Newcome

You would incorporate a range for the date and a criteria of the WEEKDAY being between two numbers. Monday - Wednesday would be 2 - 4 and Thursday - Sunday would be 5 - 7, and 1.

=COUNTIFS({Company RAID Log Range 1}, "XXXXXX", {Company RAID Log Range 2}, "CVI", {Modified Date Column}, AND(WEEKDAY(DATEONLY(@cell))>= 2, WEEKDAY(DATEONLY(@cell))<= 4))

=COUNTIFS({Company RAID Log Range 1}, "XXXXXX", {Company RAID Log Range 2}, "CVI", {Modified Date Column}, OR(AND(WEEKDAY(DATEONLY(@cell))>= 5, WEEKDAY(DATEONLY(@cell))<= 7), WEEKDAY(DATEONLY(@cell)) = 1))

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.