Formula to combine data from another sheet

mthomas1231

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.

1. Medication Count Management
2. 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

Hunter Taylor

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 each COUNTIF 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!