Need HELP Creating a Formula

Sheila DAniello

I am trying to create a formula that gives the total of each status from another spreadsheet. All statuses are in one column in the spreadsheet. As you can see, it's not working. PLEASE HELP!

Kelly Moore

Hey @Sheila DAniello

The syntax for the range changes when using cross sheet references vs when using a range on the same sheet. The first formula I gave you showed that syntax.

=COUNTIFS({Other sheet data column}, Project Status@row})

After inserting your cross sheet reference, will this formula work for you?

Kelly

If desired, you can find more info on cross sheet references in the link below

Create cross sheet references to work with data in another sheet | Smartsheet Learning Center

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Each cross sheet reference links to a single sheet. You can create multiple references to pull data from multiple sheets. 

Kelly Moore

Hey @Sheila DAniello

You mentioned another sheet - are the values (the data) you are counting in the other sheet? I understand that the name of the status is on this formula sheet

Assuming yes, the data is in the other sheet then you will need to insert a cross sheet reference from the formula window. If you need help with this, let me know. If the data is in this sheet's Project status column, use the second formula.

Data in other sheet. You cannot copy paste this formula, you must insert the reference

=COUNTIFS({Other sheet data column}, Project Status@row})

Same sheet data. You can copy paste

=COUNTIFS([Project Status]:[Project Status], [Project Status]@row)

Will either of these work for you?

Kelly

Sheila DAniello

Hi Kelly.

Thanks so much for your help. I'm getting close.

As you can see, the data is in another sheet and I tried referencing it but still get unparseable. I tried the formulas below. I'm obviously doing something wrong. I even tried selecting the column from the other sheet (see 2nd image). Any thoughts?

Again, thanks for your help!

=COUNTIFS([Project Status]:[Project Status], [Project Status]@row)

=COUNTIFS([Project Status]:Range 1, [Project Status]@row)

Kelly Moore

Hey @Sheila DAniello

The syntax for the range changes when using cross sheet references vs when using a range on the same sheet. The first formula I gave you showed that syntax.

=COUNTIFS({Other sheet data column}, Project Status@row})

After inserting your cross sheet reference, will this formula work for you?

Kelly

If desired, you can find more info on cross sheet references in the link below

Create cross sheet references to work with data in another sheet | Smartsheet Learning Center

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Each cross sheet reference links to a single sheet. You can create multiple references to pull data from multiple sheets. 

Sheila DAniello

Hi Kelly.

It's Sheila again. Is there any way I can call you? I have to present my data on Wednesday and really am not understanding why it's not working. I think 5 minutes should be good. I can send my number if you prefer.

Thank you. 😊

Kelly Moore

hey Sheila

Sure, you can send your number- I'm free now. or, we can zoom - whatever works.

Kelly

Sheila DAniello

OMG! Thank you. I can Zoom or Teams. My email is sheila.daniello@nm.org.

THANK YOU!!

Sheila DAniello

Thanks Kelly. You're absolutely awesome!!

Sheila