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

SSParks

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?

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.