Formula to combine data from another sheet
I just had surgery and I am trying to keep track of my medicine inventory. So I created an inventory log of my medicine and I have it split into 4 columns as I want to keep track of each medicine so that when I am running low I can ask for a refill. So I have two sheets.
- Medication Count Management
- Medication Management
I am trying to pull information from Medication Management into Medication Count Management. So I tried doing the following: =COUNTIF({Medication Management Range 1}, "Diazepam 2mg" + COUNTIF({Medication Management Range 2}, "Diazepam 2mg") + COUNTIF({Medication Management Range 3}, "Diazepam 2mg") + COUNTIF({Medication Management Range 4}, "Diazepam 2mg")) But it comes up with the number 0 rather than the 4 it should come up with. Am I doing the formula incorrectly, should there be an OR in there. Any other advices?
Thank you in advance! I greatly appreciate it.
-Megan
Answers
-
Hi Megan,
It looks like you’re trying to count how many times "Diazepam 2mg" appears in multiple ranges within the "Medication Management" sheet and return that total in "Medication Count Management." The issue in your formula is with the syntax. Instead of adding
COUNTIF
functions together with+
, you need to place eachCOUNTIF
in parentheses for clarity.Here's a revised formula that should work:
=COUNTIF({Medication Management Range 1}, "Diazepam 2mg") + COUNTIF({Medication Management Range 2}, "Diazepam 2mg") + COUNTIF({Medication Management Range 3}, "Diazepam 2mg") + COUNTIF({Medication Management Range 4}, "Diazepam 2mg")
Make sure that each
{Medication Management Range X}
is correctly referencing the specific column range where "Diazepam 2mg" is recorded.If "Diazepam 2mg" could appear in different columns for each entry (like primary, secondary, third, and fourth medication taken), confirm that each of these columns is correctly referenced by
{Medication Management Range X}
. In Smartsheet, each range must correspond to a specific column in the other sheet, so ensure these range references are accurately defined in your Smartsheet setup.Try this formula, and it should provide the correct count if the ranges are correctly defined. Let me know if you need further assistance!
Ask Me About Smartsheet Maps?!?!
Solving Automation, Integration, & Adoption Problems For Smartsheet Customers
Account Executive | Skyway Consulting Co
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!