# Referencing Other Sheets & Summing Text & Checkboxes

Options

Hello Again!

I am looking for the formula to sum text and value, but from another sheet. I tried using a previous formula that you guys helped me with but I can't get it to work.

I need to count the number of LM techs, Sub techs, and teams for a given job. The formula in the highlighted cells should return the number of techs assigned on a different sheet:

The sheet I am referencing looks like this:

If the text in column "FW 37" is "Cardinal Point" AND the check box in "LM Tech" column is NOT checked, it will return a "0".

If the text in column "FW 37" is "Cardinal Point" AND the check box in "LM Tech" column IS checked, it will return a "1". In this example, I should have "0" LM Techs and "3" Sub Techs.

Hope this makes sense! I can never tell if I am explaining all this correctly.

Options

Of course! 1's and 0's.....

It works now, Thank you!

Stay tuned, I am sure I will have a million more questions...

• ✭✭✭✭✭✭
Options

Are you wanting to just count how many rows have "Cardinal Point" in the [FW 37] column and the [LM Tech] box checked? If so, try this:

=COUNTIFS({Other Sheet FW 37 Column}, "Cardinal Point", {Other Sheet LM Tech Column}, 1)

I can't see the other column name in your first screenshot, but if you wanted to quickly replicate the formula for additional sections (such as "Cardinal Point", "Cardinal Corner", "Something Else") in your metrics sheet, you could avoid having to update the "specific text" by using a PARENT function like so.

=COUNTIFS({Other Sheet FW 37 Column}, PARENT([Column Name]@row), {Other Sheet LM Tech Column}, 1)

and replace "Column Name" with whatever that first column is called.

• Options

Thank you Paul! Unfortunately, the first formula didn't work. It returns "0" instead of "3". Below is your formula, substituted with the correct information:

=COUNTIFS({2020 Technician Tracking Schedule Range 1}, "TRX - Cardinal Point", {2020 Technician Tracking Schedule Range 2}, 1)

Regarding the second formula, The column name is "Site" and it does not include the "TRX" that is found on the other sheet.

Options

Of course! 1's and 0's.....

It works now, Thank you!

Stay tuned, I am sure I will have a million more questions...

• Options

One more question here.

What if I wanted to hide all the zeros that populate from that formula?

• ✭✭✭✭✭✭
Options

You could use an IF statement.

=IF(COUNTIFS(.......) = 0, "", COUNTIFS(.......))

• Options

Got it! Thank you!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!