COUNTIFS Referencing Other Sheets

Jill Youngberg

Needing help with the COUNTIFS and referencing other sheets. It keeps coming back #unparseable

I am trying to find the number of projects in a certain phase based on who the project is assigned to.

Active Leads Range 11: Assigned to

Active Leads range 10: Phase

. =COUNTIFS({Active Leads Range 11}:{Active Leads Range 11}, "Mark",{Active Leads Range 10},"Bidding")

 

 

I am sure I am completely off, but any help would be appreciated.

Chris McKay

Hi Jill,

You were actually really close! When you specify a range on the source sheet (e.g. Active Leads), you only need to reference the range (i.e. not specify it again) on the target sheet as the range already includes information about what sheet, columns and rows you're matching criteria against.

That means your formula will look like this:

=COUNTIFS({Active Leads Range 11}, "Mark",{Active Leads Range 10}, "Bidding")

Hope that helps!

Cheers,

Chris

Jill Youngberg

Chris -

 

Thank you! That helps a lot. One more question - is there a way to reference 2 sheets in 1 formula. 

I have 1 sheet that is for Active Leads and 1 sheet that is for Active Projects. I would like to combine the count based on the same data for both of these sheets.

=COUNTIFS({Assigned To}, "Mark", {Phase}, "New Lead", {Active Projects - Assigned To}, "Mark", {Active Projects - Phase} "New Lead")

The formula above is coming back #unparseable again... Thoughts?

Chris McKay

Hi Jill,

I see you've got the hang of creating named ranges. Well done.

In answer to your question, while you can certainly reference more than one sheet in a formula, I don't believe Smartsheet cross-sheet references (the { } references) allow you to do what you want inside a COUNTIF/COUNTIFS statement.

If you are looking to combine the count (i.e. add up all the times Mark is assigned to an Active Lead or Active project), you can do this:

=COUNTIFS({Assigned To}, "Mark", {Phase}, "New Lead") + COUNTIFS({Active Projects - Assigned To}, "Mark", {Active Projects - Phase} "New Lead")

Kind regards,

Chris McKay

Burke M Rich

I have a question on this same line.



I have a list of Projects that also includes that Data Services person that is on the project. I want to get an active count of the total Projects assigned to both the Project Manager and the Data Services Engineer. I have made the following in a new sheet:

=COUNTIFS({Project Management - Load}, "Adam Beach",{Project Management - Load}, "In Progress")

I get back a response of #INVALID REF.

What am I doing wrong?

Andrée Starå

Hi Burke,

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Rebecca Lane

Hello,

I was following this thread as I am trying to create a countifs formula to add data from 2 separate sheets. I've tried multiple formulas to see if I could make it work, but I continue to get an "Unparseable" error or an "Incorrect Argument Set" error.

Unparseable error is with:

=COUNTIFS({2021 L2R After Hours Raw Data Range 1}, "L2R After Hours Inquiry- Tuition", {2021 L2R After Hours Raw Data Range 3}, "Name", {2021 L2R After Hours Inquiry- Tuition Open Range 1}, "{2021 L2R After Hours Inquiry- Tuition Open Range 2}, "Name")

Incorrect Argument Set error is with:

=COUNTIFS({2021 L2R After Hours Inquiry- Tuition Open Range 2}, "Name", {2021 L2R After Hours Inquiry- Tuition Open Range 3}, "L2R After Hours Inquiry- Tuition Open" + COUNTIFS({2021 L2R After Hours Raw Data Range 3}, "Name", {2021 L2R After Hours Raw Data Range 1}, "L2R After Hours Inquiry- Tuition"))

Overall, I am trying to determine the number of Tuition and Tuition Open inquiries for a person. Hoping someone can help.

Thanks,

Rebecca

Genevieve P.

Hi @Rebecca Lane

For your first formula, it looks like there's an additional quotation mark in front of your final range:

here > "{2021 L2R After Hours Inquiry- Tuition Open Range 2}, "Name")

However, since you're looking into two different sheets, you will need to add together two COUNTIF statements like you have set up in your second option, instead of combining them in one formula. Each individual formula will need to look into just one sheet, then you can add the result together.

Your second formula needs to have each COUNTIF function closed off with one of these ) before you add in the next COUNT. Try this:

=COUNTIFS({2021 L2R After Hours Inquiry- Tuition Open Range 2}, "Name", {2021 L2R After Hours Inquiry- Tuition Open Range 3}, "L2R After Hours Inquiry- Tuition Open") + COUNTIFS({2021 L2R After Hours Raw Data Range 3}, "Name", {2021 L2R After Hours Raw Data Range 1}, "L2R After Hours Inquiry- Tuition")

Cheers!

Genevieve

Rebecca Lane

Hi Genevieve,

Thanks so much!

Genevieve P.

No problem at all, @Rebecca Lane! 🙂