Create a Table Summary

[albert_de]

Hi

I am trying to workout how to create a specific summary of a table of sales information that is on a separate worksheet of a workbook. It's probably quite simple but I haven't been able to work it out yet!

For example the Data Table (Worksheet) might look like this:

The Summary Table has a Drop List to select the sales product from the Data sales. Selecting the product will return a sales list that includes all dates with sales figures in it. A sales date that does not have any sales figure entered against it should not be included in the summary table.

The Summary Table (worksheet) would look like this:

I don't know if a Pivot Table will work? And I haven't been able to workout a suitable "Filter" or XLookup" formula etc. So I am a bit lost now on how to create the Summary Table.

Is there an expert that can kindly guide me!

 

[arthurbr]

First use Power Query to unpivot your original table, then use the loaded result as data source for your filtering.

 

[albert_de]

Hi arthurbr, thank you for your reply. I hadn't completed the creation of the pivot table because I thought that the formatting of the table would not work for a pivot table. But I will give this another go and see what happens as per your instructions.

​

 

[Eric W]

Maybe:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2
3
4							1/1/2025	1/2/2025	2/2/2025	3/3/2025
5						Product	$ 22.50	$ 83.75	$ 57.55	$ 20.30	Sales Total
6						Bar	$ 16.00	$ 18.25		$ 15.30
7						Glue	$ 5.00	$ 5.00	$ 5.00	$ 5.00
8						Nail		$ 60.50	$ 50.55
9						Tube	$ 1.50		$ 2.00
10
11
12
13				Select Product:	Bar
14
15				All Results	Date	Value		Min	Max	Variation
16					1/1/2025	$ 16.00		$ 15.30	$ 18.25	16.2%
17					1/2/2025	$ 18.25
18					3/3/2025	$ 15.30
19
Sheet5

Cell Formulas
Range	Formula
G5:J5	G5	=SUM(G6:G9)
E16:F18	E16	=LET(a,FILTER(G6:J10,F6:F10=E13),b,FILTER(a,a<>""),d,FILTER(G4:J4,a<>""),CHOOSE({1,2},TRANSPOSE(d),TRANSPOSE(b)))
H16	H16	=MIN(INDEX(E16#,0,2))
I16	I16	=MAX(INDEX(E16#,0,2))
J16	J16	=(I16-H16)/I16
Dynamic array formulas.

 

[albert_de]

Hi Eric W, your Formula for E16 is the concept that I am looking for. Thank you very much. I wouldn't have figured that out for myself!
I have applied the E16 formula to the actual spreadsheet and I am getting a #CALC! Error.
I will try and see where my translation of your E16 formula has gone wrong for me and report back.

 

[Eric W]

You might get a CALC error if there are no matches, either on the part name, or if there are no values for that part.

 

[albert_de]

Understood, thank you for the advice.
In the test that I am running on the actual spreadsheet, there is actual Product data to match against (which should be identical - i.e. E16 is a List sourced from F6:F10) and with values against some of the Dates in the Date range.
I'll keep testing

 

[albert_de]

I have tried adding & translating the E16 formula into the workbook example shown at the start of this thread. But I haven't have any luck with it yet. It also creates #CALC! errors.
There are 2 worksheets - Data and Summary. The screenshot below is from the Summary worksheet containing the Filter formula. E5 contains the (Product Name) List generated from the Data Worksheet.

 

[Eric W]

The part of the formula that says:

,Summary!F6:F10=E5

should be

,Data!F6:F10=E5

 

[albert_de]

That worked on the test Workbook...thank you for the correction. Let's see if I can do the same on the actual Workbook!