How to create a rule that calculates the number of selections in a cell with another cell

I am trying to create a rule that calculates the number of selections in one cell and divides this with a $ value in another cell and looks for another choice in a separate column and calculates the total value for that % of that choice.

Example

No of values in one cell is 5 and the total budget value in this row is $10,000. The result would therefore be $2,000. I want to be able to then create a figure for each of the 5 options in a separate calculation sheet that looks for the total value in all rows.eg Sales.

I have done a manual workaround as follows by creating 2 separate columns in the sheet {Budget Calculations Range 1} sheet and then looks for the option "Sales" in the column in the same sheet {Main Sheet name} in all rows:

=SUMIF({Main Sheet name}, CONTAINS("Sales", @cell), {Budget Calculations Range 1})

I want to be able to not create the 2 division columns and have one rule that does everything.

Any help much appreciated.

Answers

  • Mary_A
    Mary_A ✭✭✭✭✭✭

    Can you give an example row please?

  • Leibel S
    Leibel S ✭✭✭✭✭✭

    @mike.thorpe17421

    Sounds you like you want to do this:

    =IFERROR(SUM([total budget value]@row / COUNTM(Selections@row)), "")

  • mike.thorpe17421
    mike.thorpe17421 ✭✭✭✭✭

    Name of the Sheet that contains all of the data: "Tactical Planning"

    Name of the Column which contains the required choice: "Indication"

    Name of the choice from the "Indication" Column: "PNH"

    Name of the column that contains the budget figure: "Total budget conversion to USD $"

    I have created a calculations sheet and want to reference the "Tactical Planning" sheet. If there are 9 rows that contain "PNH" but in those cells some may have 3 choices some may have 4, some may have 5 so the budget figure for each cell will be different depending on the number of choices and the value of the "Total budget conversion to USD $" cell.

    Does this explain as I couldn't get the =IFERROR(SUM([total budget value]@row / COUNTM(Selections@row)), "") formula to work.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!