Count a person from 2 different grids and drop-down vs text box?

Greetings,
I'm trying to figure out if there is a way to count a person if they show up in two different sheets with two different selection options. For example, user Amanda Perry could be on sheet 1 listed under the programmer column from a drop-down where the user is only able to select one programmer. User Amanda Perry may also be listed on a totally separate grid/sheet and selected from a drop-down (Programmers)where other people are also selected. So her name would be one of three people in that text field, for example.

Is there a way to total how many times Amanda Perry was listed as a programmer from two different sheets with both of the cells being different? My end result is I want to have a total of how many times Amanda Perry was listed or selected as a programmer from two separate sheets. Essentially, I'm trying to get a yearly total of how many programs she did or was listed for from two different locations, but I want the result to be a single result totaled. Thank you for any info or help!

This formula counts Amanda Perry if she is listed and it is in 2024 from sheet1
=COUNTIFS({Previous Years Public Form Range 2}, "2024", {Previous Years Public Form Range 3}, "Amanda Perry")

This formula counts Amanda Perry with others from sheet2
COUNTIFS({Community Requests Range 1}, HAS(@cell, "Amanda Perry"))

Is there a way to combine these 2 formulas that count Amanda Perry from 2 sheets in 2024?

Find more posts tagged with

Formulas

Countif

Accepted answers

All comments

Jason Tarpinian

So it looks like your individual formulas are working? Can you not just add the COUNTIFS together, or am I missing something?

=COUNTIFS({Previous Years Public Form Range 2}, "2024", {Previous Years Public Form Range 3}, "Amanda Perry") + COUNTIFS({Community Requests Range 1}, HAS(@cell, "Amanda Perry"))

SSParks

https://community.smartsheet.com/discussion/comment/467930#Comment_467930

I can get that formula to work, but I need to add the year 2024 to that second part of the formula and not sure how. The community request range sheet has a column called DateYear and I wanted to count if it says her name plus the year 2024 in that column. What am I missing?

=COUNTIFS({Previous Years Public Form Range 2}, "2024", {Previous Years Public Form Range 3}, "Amanda Perry") + COUNTIFS({Community Requests Range 1}, HAS(@cell, "Amanda Perry"))

SSParks

https://community.smartsheet.com/discussion/comment/467930#Comment_467930

This formula isnt working. Im trying to add the "2024" from Community Requests Range 2 so I can add a count if her name shows and if its in 2024. Thoughts?

=COUNTIFS({Previous Years Public Form Range 2}, "2024", {Previous Years Public Form Range 3}, "Amanda Perry") + COUNTIFS({Community Requests Range 1}, HAS(@cell, "Amanda Perry"), {Community Requests Range 2}, "2024")

Jason Tarpinian

What type of column is {Community Requests Range 2}? Try replacing 2024 as below, you are matching 2024 as a text value when you wrap it in quotations, without quotations it will match it as a number.

=COUNTIFS({Community Requests Range 1}, HAS(@cell, "Amanda Perry"), {Community Requests Range 2}, 2024)

It's just odd that the Previous Years Public sheet it working, but I'd double check that one without the quotations too.

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.