Shawn Miller
New Member
Hello,
My title describes what I am trying to do but this may not be the best approach. In the attached file, I am trying to create a formula that will give me a revenue total (Vol*Rate) for all customers where the active scenario column = TRUE and the company column = Comp Alpha. In yellow I have the math manually calculated by manually referencing the cells that should be included. I have highlighted in blue the rows impacted. This is just an example, ultimately I want to have revenue by summing up all the individual customers vol*rate where the active scenario is TRUE and be able to sum this by company. I have tried using SUMPRODUCT but can only get it to add (I need to multiple rate*vol for each customer based on this criteria). Also I can pivot to get this view but prefer a way to do this in the table to make it cleaner.
Does anyone have any suggestions on way to make this work so that I have a formula giving me the values in yellow so this would change if I change the data? Perhaps a helper column or a more elegant approach?
Thank you!
My title describes what I am trying to do but this may not be the best approach. In the attached file, I am trying to create a formula that will give me a revenue total (Vol*Rate) for all customers where the active scenario column = TRUE and the company column = Comp Alpha. In yellow I have the math manually calculated by manually referencing the cells that should be included. I have highlighted in blue the rows impacted. This is just an example, ultimately I want to have revenue by summing up all the individual customers vol*rate where the active scenario is TRUE and be able to sum this by company. I have tried using SUMPRODUCT but can only get it to add (I need to multiple rate*vol for each customer based on this criteria). Also I can pivot to get this view but prefer a way to do this in the table to make it cleaner.
Does anyone have any suggestions on way to make this work so that I have a formula giving me the values in yellow so this would change if I change the data? Perhaps a helper column or a more elegant approach?
Thank you!
Attachments

141.3 KB Views: 13