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
-
Can you give an example row please?
-
Sounds you like you want to do this:
=IFERROR(SUM([total budget value]@row / COUNTM(Selections@row)), "")
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!