COUNTIFS / Referencing data another sheet

Nic Larsen

I'm still pretty new to Smartsheet. My goal is collect data from another sheet in an easier to read format but I cannot get my COUNTIFS formula to work. If I do separate formulas for each criteria it will work but not when I attempt to combine the formulas to look at 2 criteria's it fails. 

I have columns on the referencing sheet with Team Names (accounting, finance, compliance, etc) and Status column (On Time, Complete, Late, etc). I have inserted references on that range of columns.

Reference sheet is "Program1"

=COUNTIFS({Program1}, "Accounting", {Program1}, "Complete")

=COUNTIFS ({Program1}, "Accounting", {Program1}, "Late")

I want a total count of when the team name has a task showing as complete, late, etc.

I have researched up and down and cannot get anything other than an "unparseable" return.

What am I missing? Any help is appreciated.

 

Paul Newcome

You need to set the range on the reference sheet. Typically when you reference a portion of another sheet in a formula it would be something to the effect of {Program1 Range 1} to look something like this in a formula:

 

=COUNTIFS({Program1 Range 1}, "Accounting", {Program1 Range 2}, "Complete")

 

The easiest way to accomplish this is by selecting the "Reference Another Sheet" link in the help box that pops up as you type your formula (see images below and please disregard the sloppy lines as I am certainly NOT an artist. Haha).

 

Otherwise, your formulas seem to be pretty well set.

Nic Larsen

Thank you the ranges were the key. I was selecting a single range of multiple columns. Once I split it up it worked. Thanks!