Referencing Other Sheets & Summing Text & Checkboxes
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.
Best Answers
-
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.
-
Of course! 1's and 0's.....
It works now, Thank you!
Stay tuned, I am sure I will have a million more questions...
Answers
-
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.
-
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.
-
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.
-
Of course! 1's and 0's.....
It works now, Thank you!
Stay tuned, I am sure I will have a million more questions...
-
One more question here.
What if I wanted to hide all the zeros that populate from that formula?
-
You could use an IF statement.
=IF(COUNTIFS(.......) = 0, "", COUNTIFS(.......))
-
Got it! Thank you!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!