• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMPRODUCT with multiple criteria for values in same column

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!
 

Attachments

  • Excel Example - SUMPRODUCT with Criteria in same column.xlsx
    141.3 KB · Views: 19
I have used INDEX to build new arrays of half the size by drawing on every alternate row. Here I have used helper ranges to illustrate the process. In practice, I would let the defined names, presently used to reference the helper ranges, refer to the formula they contain directly.

Although, for me, such techniques are the basic bread-and-butter from which I build all solutions, they are far from mainstream and most spreadsheet users would not recognise them as Excel.
 

Attachments

  • SUMPRODUCT with Criteria in same column.xlsx
    148.6 KB · Views: 10
For what it is worth, I have uploaded a dynamic array version of the above in which all the formulae are held as names and no helper cells are used.
For this to work you will need Office 365 set to Insider Channel to load a beta-release version of Excel.
 

Attachments

  • SUMPRODUCT with Criteria in same column (DA).xlsx
    147.5 KB · Views: 16
Peter - thank you for the feedback. I do have Office 365 but do not recognize any part of your second, more elegant solution! The first solution I can follow but my concern is if the sales team starts sorting and moving data around in these tables that contain vol/price. I am trying to re-design this as the "input" for a larger valuation model. Do you have any suggestions for an easy to use table that captures vol/price where we can clear and dump this data in and be able to derive total revenue easily?
 
As such with your posted layout it will be possible to get the same results by using below formula for cell H7 and then it can be copied across.

=SUM((H11:H21*H12:H22)*(($B$11:$B$21="Comp Alpha")*($G$11:$G$21=TRUE)*($F$11:$F$21="Vol.")))

This can be surely reduced and simplified if you create Rate and Vol for each year in a two-columnar layout instead of alternating rows of data in one column i.e. Parts H11:H21*H12:H22 and $F$11:$F$21="Vol.") respectively .
 
The analysis you require is well-suited to pivot table analysis but, ideally, that would require the data to be input in normalised form.
You have a number of choices. @shrivallabha has made offset ranges work in the context of traditional Excel formulas. Both that approach and the dynamic array solution I posted yesterday rely upon the alternating pattern of Volume and Rates.

The attached uses FILTER to separate volumes and rates at the same time as applying output filters on the other columns. This makes the solution more robust but you will need to wait until autumn/fall by which time your copy of Office 365 should have dynamic arrays. Alternatively, to be able to use FILTER and XLOOKUP now, your IT manager would need to switch you to the Insider Channel rather than semi-annual.

The other possibility that you could implement now is to use Power Query to unpivot the Year columns and pivot the volumes and rates to separate fields. From that point pivot tables would allow you to aggregate and present the results in any way you choose.

p.s. I am no data analyst but I have also included a Power Query solution with a pivot table.
 

Attachments

  • Criteria in same column (DA and PQ).xlsx
    157.1 KB · Views: 6
Last edited:
Thanks Shrivallabha! I plugged this in and it works but my concern is if I start sorting data in the table it can "break" in the sense that the total will change. Sorting will inevitably happen with our sales team as they will sort by customer or want to sort by volume or price for different reasons.
 
Thanks Shrivallabha! I plugged this in and it works but my concern is if I start sorting data in the table it can "break" in the sense that the total will change. Sorting will inevitably happen with our sales team as they will sort by customer or want to sort by volume or price for different reasons.
Yes! such formulas rely solely on pattern being consistent. The risk is any change in pattern may not necessarily result in a visible error but may instead return unintended calculation outcome!

That is why I suggested a two column output (Vol and Rate on the same row) where all relations are on a single row and therefore sorting shouldn't affect the formula outcome.
 
Back
Top