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.

Find more posts tagged with

Accepted answers

All comments

Mary_A

Can you give an example row please?

Leibel S

@mike.thorpe17421

Sounds you like you want to do this:

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

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.