Data selection using drop down options

[Andyd1234]

Hi,

Got my original formula in a separate thread but looking to expand this if possible?

The formula in line 6 pulls the relevant data from the selected drop down options in cells B3 & E3

I am now trying to split this data as per contract status in blue highlighted cells (Which when totalled will match the data populated in line 6)


Any ideas what the best formula would be?

Another request, although I don't think possible, would be to then list the sales orders in line 14 down showing only the movements between the 2 selected dates? (i.e. list the sales order number and what has moved - status change, order value change. I would not want to show any sales orders that have no movement)

Many thanks again for your assistance

[JohnTopley]

"Windows 11" is not an Excel version so please update your profile with correct version (365 + release no, 2021,2019 ......) as responses do depend on knowing this.

I would also reconsider changing the formatting (multiple pages/dates) for your contract information to a single sheet with the various "Status" and corresponding dates as column headings.

[Andyd1234]

Thanks John,

I have updated my version to Office 365

The attached was a sample sheet of what I am trying to achieve. My working book has many other columns which are required in separate tabs.

[JeteMc]

This is an 'old school' solution:
1. Build count and sum ranges on each of the 0103, 0203 and 0303 sheets
The formula for qty is in cell F3: =COUNTIFS(B$3:B$12,E3)
The formula for value is in cell G3: =SUMIFS(C$3:C$12,B$3:B$12,E3)
2. On the summary sheet the formulas for qty and value is: =INDIRECT("0"&$B$3&"!"&ADDRESS(ROWS(B$1:B3),COLUMNS($A$1:F$1)))
Note that I didn't change the formulas in H9:I11 and don't know if they are displaying the expected values based on post #1.
Let us know if you have any questions.