What is wrong with my formula?

Orlando Mercado

I am trying to have smartsheet count if the benefit is efficiency gain and the status in in progress. The benefits and status columns do have multiple selections to choose from and some. The benefits column can have more than just efficiency gain selected from a dropdown. I'm using the formula below. The benefits and status are referenced through another smartsheet.

=COUNTIFS({Benefits}, "Efficiency Gain", {MH-Work Prioritization Log Range 1}, "In Progress")

What am I missing?

Frank B.

The formula you provided seems correct. However, it's possible that the cell ranges referenced in the formula are not correctly set up or there might be some other error in the sheet. Here are some steps you can follow to troubleshoot:

1. Double-check the cell ranges being referenced in the formula. Make sure that the correct range is selected and that there are no typos in the range names.
2. Check the data in the "Benefits" and "Status" columns to ensure that the values match the criteria you specified in the formula. For example, make sure that "Efficiency Gain" is spelled correctly and that the status is indeed "In Progress".
3. Ensure that the formula is in the correct format. Smartsheet uses comma-separated arguments for its functions, so make sure that there is a comma between each argument.
4. If the issue persists, try simplifying the formula and test it with just one condition. For example, try using the following formula to count the number of tasks in progress: =COUNTIFS({MH-Work Prioritization Log Range 1}, "In Progress")

Orlando Mercado

I tried the formula you suggested and it will give me the number "In Progress", which is good. Now, I'm trying to incorporate the second part of the formula to have the count capture any project that has Efficiency Gain, but now I get a ZERO. There are several projects with the benefit of Efficiency Gain and not sure why its not being captured. I tried two formulas below.

=COUNTIFS({MH-Work Prioritization Log Range 1}, "In Progress", {MH-Work Prioritization Log Range 1}, "Efficiency Gain")

=COUNTIFS({MH-Work Prioritization Log Range 1}, "InProgress", {Benefits}, "Efficiency Gain")

Keep in mind that the column is called benefits, but the sheet its pulling from is MH-Work Prioritization Log

Frank B.

It looks like there may be a couple of issues with the formulas you've tried.

For the first formula, the issue might be that you're using the same range twice within the COUNTIFS function. This means that it's looking for entries that are both "In Progress" and "Efficiency Gain" within the same cell, which is unlikely to be the case. Instead, you should use two separate criteria ranges, one for the status and one for the benefits. Try this formula instead:

=COUNTIFS({MH-Work Prioritization Log Range 1}, "In Progress", {Benefits}, "Efficiency Gain")

For the second formula, the issue might be that you've misspelled "InProgress" (without a space) instead of "In Progress" (with a space), which could be why it's not finding any matches.

In any case, using separate criteria ranges for each condition (status and benefits) should give you the result you're looking for.

Orlando Mercado

It still gave me a zero. I decided to separate the criteria into two columns. One that gave me the status "In Progress" Total by using the following formula:

=COUNTIFS({MH-Work Prioritization Log Range 1}, "In Progress")

Then another column to give me the benefits of "Efficiency Gain" by using the following formula:

=COUNTM({Benefits}, "Efficiency Gain")

I want to mention that I used countM since there are several benefits to the project other than the Efficiency Gain, but I just want to capture Efficiency Gain.

How can I make sure I'm only capturing that and am I even using the correct formula for that?

Frank B.

To ensure that you are only capturing the "Efficiency Gain" benefits, you can modify your formula to include a second criterion using the COUNTIFS function. The updated formula would look like this:

=COUNTIFS({Benefits}, "Efficiency Gain", {MH-Work Prioritization Log Range 1}, "In Progress")

This formula will count the number of cells in the "Benefits" column that contain the text "Efficiency Gain" AND the number of cells in the "MH-Work Prioritization Log Range 1" column that contain the text "In Progress". By using both criteria, you can ensure that you are only counting the cells that meet both conditions.

The COUNTM function is not necessary in this case, as you only need to count the number of cells that contain the exact text "Efficiency Gain". The COUNTIFS function can handle this task more efficiently.

I hope this helps!

Orlando Mercado

I'm still getting a zero. I am wondering why its not capturing the benefit of "Efficiency Gain". I wonder if its because its not the only benefit in the cell. I have 3 other benefits in the same cell.