COUNTIFS Referencing Other Sheets

COUNTIFS Referencing Other Sheets

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 McKayChris 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!



  • 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 McKayChris 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

  • 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å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, [email protected])

    Hope that helps!

    Have a fantastic week!


    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD



    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

Sign In or Register to comment.