Make a table based on all options in a Data Validation drop-down

[SouthOxon1997]

Hi

I have an extensive financial forecast, from which I have created the very simplified example attached.
The Input page lists all revenue expected, by customer, product and month, with a Risk level (Low, Medium, High) assigned to each one. The Calc Revenue column only includes the particular line's revenue if the Company Revenue Risk level (at the top in yellow) is at least as high as that assigned to each row. Ie if a given row is High risk, and Company Revenue Risk is set to Medium, that row will be excluded.

I have a need to put together a table, and then a chart showing Revenue by month for each of the three Company Revenue Risk levels (three lines on the same chart). (I also have the need to do the same for various other calculated items like cash level and stock held based on the same three Company Revenue Risk levels, but those sheets take in a whole load of other inputs, so I have excluded them from my example attached.)

Problem is that Low, Medium and High are never calculated and shown at the same time, so I can't populate a table with the three rows I need. I thought about using a Data Table, but I can't work out how to do this, as the Company Revenue Risk level seems to be too remote from the revenue (let alone the cash or stock) calculations.

Any thoughts welcome, and thanks in advance.

S

[Glenn Kennedy]

In B13, copied across:

=SUMPRODUCT((Input!$E$4:$E$39=Summary!B$12)*(Input!$D$4:$D$39=$A13)*Input!$G$4:$G$39)

In B14, copied across and down:

=B13+SUMPRODUCT((Input!$E$4:$E$39=Summary!B$12)*(Input!$D$4:$D$39=$A14)*Input!$G$4:$G$39)

see file.

[SouthOxon1997]

Many thanks Glenn, that works a treat for the revenue table.
For a follow-on question, as I alluded to in the original post, I would like similar Low Medium High tables for other elements of my financials, initially cash balance and stock levels. It is only revenue that has the Low Medium High classifications, but this feeds into the cash and stock calculations. Eg, revenue for each product for each month is used to work out how much of each product will be bought and when, based on minimum stock levels and minimum order quantities, and there are sales commissions too. Without duplicating all of these calculations for each revenue risk level, or using a macro (something I have never done), can you see a way to generate these tables?
Thanks
S

[Glenn Kennedy]

RoFLAO... you are talking to an analytical chemist... and that torrent of words was incomprehensible to me!!

You mock it up, show me where the numbers come from & I'll give it a go!!

[SouthOxon1997]

Thanks Glenn
It may take me some time to chop it down into the bare bones that I need you to look at, but I'll try and find the time for that next week. Needs to be slimmed down enough to be comprehensible, but not so much that it is over-simplistic.
Thanks
S