Using Countifs with multiple references to other sheets

Mark Case

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")

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