Referencing Other Sheets & Summing Text & Checkboxes

Amanda Medina

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.

Paul Newcome

The first formula is counting checked boxes in the [LM Tech] column. If you instead wanted to count unchecked boxes, you would need to swap the last 1 out for a 0 (zero).

The second formula is referencing the cell in your metrics sheet that contains "Cardinal Point" and looking down the [FW 37] column in your reference sheet for a match. It is just an easy way to update a "specific text" portion if you need to replicate the formula.

Amanda Medina

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

It works now, Thank you!

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

Paul Newcome

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.

Amanda Medina

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.

Paul Newcome

The first formula is counting checked boxes in the [LM Tech] column. If you instead wanted to count unchecked boxes, you would need to swap the last 1 out for a 0 (zero).

The second formula is referencing the cell in your metrics sheet that contains "Cardinal Point" and looking down the [FW 37] column in your reference sheet for a match. It is just an easy way to update a "specific text" portion if you need to replicate the formula.

Amanda Medina

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

It works now, Thank you!

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

Amanda Medina

One more question here.

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

Paul Newcome

You could use an IF statement.

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

Amanda Medina

Got it! Thank you!

Paul Newcome

Happy to help. 👍️