Using Countifs with multiple references to other sheets

Been playing with this for a while but can not seem to get this right.

Any help would be appreciated

=COUNTIFS({Editing List-3-2018 Range 2}, >=(DATE(2018, 7, 1), {Editing List-3-2018 Range 5}, ="Name"), Date:Date, <=(DATE(2018,6,30), [Assigned to: Editor]:[Assigned to: Editor],="Name")

Find more posts tagged with

Education

Formulas

Project Management

Comments

Brian W

I think you have some extra parentheses. Try this:

=COUNTIFS({Editing List-3-2018 Range 2}, >=DATE(2018, 7, 1), {Editing List-3-2018 Range 5}, ="Name", Date:Date, <=DATE(2018,6,30), [Assigned to: Editor]:[Assigned to: Editor],="Name")

Mark Case

Thank you Brian,

That got me away from #UNPARSEABLE

However, it has now given me a #INCORRECT ARGUMENT SET.

=COUNTIFS({Editing List-3-2018 Range 2}, >=DATE(2018, 7, 1), {Editing List-3-2018 Range 5}, ="Name", Date:Date, <=DATE(2018, 6, 30), [Assigned to: Editor]:[Assigned to: Editor], ="Name")

Do you see anything else that I am missing?

Thanks, I appreciate the help.

Mark C.

Brian W

I think I see the issue. It looks like you are referencing two different sheets in your COUNTIFS formula. Are the Date and Assigned to: Editor columns supposed to be referencing the Editing List-3-2018 sheet?

Mark Case

Yes,

I am referencing the Editing List-3-2018 twice 1 for the date range the other for the name range, and then 2 different columns on the sheet I am placing the formula, 1 is the date range and the other is the name range.

My video manager keeps track of his video projects by Calendar Year and my boss need the information broken down into fiscal year. So I need to count the dates above 7/1/2018 on the one page and the dates below 7/1/2018 on the other page.

Hope that helps.

Thank you again.

Mark C.

Brian W

Okay. I think I got it. What you need to do is calculate 2 separate COUNTIFS formulas (one for each sheet) and add them together. Try this:

=COUNTIFS({Editing List-3-2018 Range 2}, >=DATE(2018, 7, 1), {Editing List-3-2018 Range 5}, ="Name") + COUNTIFS(Date:Date, <=DATE(2018,6,30), [Assigned to: Editor]:[Assigned to: Editor],="Name")

Mark Case

THANK YOU!

That did it.

So am I not able to reference more than 1 sheet per formulae?

Thanks again

Mark C

Brian W

Great. I'm glad it worked. I think you can pull from multiple sheets for some functions, but not COUNTIFS because it checks for matching criteria in a single row. Since you are using two sets of criteria ("check this group of rows for a matching date and name, and also this other group for a matching date and name"), you need to use two separate COUNTIFS. If that makes sense...

pageella

Hi, I am trying to do a similar thing.

I have multiple sheets that list site names, I would like to count how many times that site appears across all of the sheets. But I can't quite get it to work...

=COUNTIFs({AMER - Ops CE Tracker - 2023 Range 1}, [Site Name]@row,) + COUNTIFS({CEE - Ops CE Tracker - 2023 Site Name}, [Site Name]@row)

thanks!

Genevieve P.

Hi @pageella

It looks like you just have an extra comma in your formula, which would cause an UNPARSEABLE error.

Try this:

=COUNTIFS({AMER - Ops CE Tracker - 2023 Range 1}, [Site Name]@row) + COUNTIFS({CEE - Ops CE Tracker - 2023 Site Name}, [Site Name]@row)

If you're on a Business or Enterprise plan, another option would be to create a Report using both sheets for the source, then Group by the Site Name and Summarize to get a count.

Cheers,

Genevieve

Sign in to join the conversation!

Quick Links

Trending discussions

Multiple conditions for counting
Super-basic question regarding COUNTIFS. I'm trying to return a value that shows me a count of projects from a certain year, for a certain client. =COUNTIFS((Project Number:Project Number), Contains("2025", @cell ),(Client:Client), Contains("ADV", @cell )) But this comes up as unparseable. Where am I going wrong?
Editing Sheet Summary 'CY' Current Year - so my formulas update
I am attempting to enter the CY - Current Year into my sheet summary so I don't have to go through and update every cell formula when creating the new year's metrix sheets. What is the correct syntax in my formula so both CY are pink, indicating it references the CY in the Sheet Summary?
Stumped with formula generated through AI helper
I recently created a formula using the AI tool, but it's returning #INCORRECT ARGUMENT SET and I can't figure out why. The prompt that I entered was: Count [Project] where [FY26] includes "Yes" or is blank, and where [Project Status] is not "Completed" or "Cancelled" The formula that the tool returned was:…