How to make the report without using pivotable?

[Sochen]

Hi All,

I have two reports below which need to fill in yellow cell.
Report 1: Find the total amount, receipt and units of each Salesperson between date.
Note: Special Plastic bag, Credit off, Free repair and Free Gift will not count the qty/receipt as sale.

Report 2: Prepare daily sales report which need to count unique receipt and remove all Special Plastic bag, Credit off, Free repair and Free Gift.
Note: Do not include Special Plastic bag, Credit off, Free repair and Free Gift in sale

Please help with office 2019 formula as my work place used excel 2019, while only my personal laptop used office 2021.
Thank you!

Please download full report from the link belows:
Sales Report.xlsx

Thank you!

Sales Report.xlsx
	H	I	J	K	L	M	N	O	P	Q
1	Report 1						Report 2
2
3	Start:	23-Jan-23		To:	29-Jan-23
4
5	Rank	Seller ID	Total Sales	Total Receipts	Total Units		Date	Total Sales	Total Receipts	Total Units
6	1	111	$ 865.00	24	34		8-Jan-23	$ 822.00	14	19
7	2	133	$ 537.00	16	22		9-Jan-23	$ 455.00	15	20
8	3	144	$ 511.00	16	21		10-Jan-23	$ 416.00	13	15
9	4	155	$ 437.00	10	12		11-Jan-23	$ 428.00	14	18
10	5	166	$ 363.00	8	16		12-Jan-23	$ 444.00	16	17
11	6	122	$ 266.00	8	12		13-Jan-23	$ 566.00	11	15
12	Total		$ 2,979.00	82	117		14-Jan-23	$ 524.00	12	17
13							15-Jan-23	$ 418.00	11	19
14							16-Jan-23	$ 787.00	18	20
15	Please note:						17-Jan-23	$ 396.00	16	17
16	Special Pastic bag		Not count as sale				18-Jan-23	$ 800.00	15	19
17	Credit Off		Not count as sale				19-Jan-23	$ 334.00	7	12
18	Free Repair		Not count as sale				20-Jan-23	$ 233.00	6	8
19	Free Gift		Not count as sale				21-Jan-23	$ 277.00	6	11
20							22-Jan-23	$ 602.00	14	22
21							23-Jan-23	$ 588.00	15	21
22							24-Jan-23	$ 464.00	11	14
23							25-Jan-23	$ 402.00	17	26
24							26-Jan-23	$ 554.00	12	17
25							27-Jan-23	$ 314.00	7	10
26							28-Jan-23	$ 331.00	11	17
27							29-Jan-23	$ 326.00	8	12
Sheet1

Cell Formulas
Range	Formula
J12:L12	J12	=SUM(J6:J11)

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to create a dynamic report?
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
However, since you have been advised previously: How to sum and rank with multi criteria uniquely without duplicate? For excel 2019 if you continue with cross-posting without links sterner action may follow.

 

[Sochen]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to create a dynamic report?
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
However, since you have been advised previously: How to sum and rank with multi criteria uniquely without duplicate? For excel 2019 if you continue with cross-posting without links sterner action may follow.

Dear Peter,
My sincere apologies.
I have shared the link to another forum.
Again, my apologies for this.

Thank you!

 

[Sochen]

Hi All,

I have two reports below which need to fill in yellow cell.
Report 1: Find the total amount, receipt and units of each Salesperson between date.
Note: Special Plastic bag, Credit off, Free repair and Free Gift will not count the qty/receipt as sale.

Report 2: Prepare daily sales report which need to count unique receipt and remove all Special Plastic bag, Credit off, Free repair and Free Gift.
Note: Do not include Special Plastic bag, Credit off, Free repair and Free Gift in sale

Please help with office 2019 formula as my work place used excel 2019, while only my personal laptop used office 2021.
Thank you!

Please download full report from the link belows:
Sales Report.xlsx

Thank you!

Sales Report.xlsx
	H	I	J	K	L	M	N	O	P	Q
1	Report 1						Report 2
2
3	Start:	23-Jan-23		To:	29-Jan-23
4
5	Rank	Seller ID	Total Sales	Total Receipts	Total Units		Date	Total Sales	Total Receipts	Total Units
6	1	111	$ 865.00	24	34		8-Jan-23	$ 822.00	14	19
7	2	133	$ 537.00	16	22		9-Jan-23	$ 455.00	15	20
8	3	144	$ 511.00	16	21		10-Jan-23	$ 416.00	13	15
9	4	155	$ 437.00	10	12		11-Jan-23	$ 428.00	14	18
10	5	166	$ 363.00	8	16		12-Jan-23	$ 444.00	16	17
11	6	122	$ 266.00	8	12		13-Jan-23	$ 566.00	11	15
12	Total		$ 2,979.00	82	117		14-Jan-23	$ 524.00	12	17
13							15-Jan-23	$ 418.00	11	19
14							16-Jan-23	$ 787.00	18	20
15	Please note:						17-Jan-23	$ 396.00	16	17
16	Special Pastic bag		Not count as sale				18-Jan-23	$ 800.00	15	19
17	Credit Off		Not count as sale				19-Jan-23	$ 334.00	7	12
18	Free Repair		Not count as sale				20-Jan-23	$ 233.00	6	8
19	Free Gift		Not count as sale				21-Jan-23	$ 277.00	6	11
20							22-Jan-23	$ 602.00	14	22
21							23-Jan-23	$ 588.00	15	21
22							24-Jan-23	$ 464.00	11	14
23							25-Jan-23	$ 402.00	17	26
24							26-Jan-23	$ 554.00	12	17
25							27-Jan-23	$ 314.00	7	10
26							28-Jan-23	$ 331.00	11	17
27							29-Jan-23	$ 326.00	8	12
Sheet1

Cell Formulas
Range	Formula
J12:L12	J12	=SUM(J6:J11)

Hi everyone,
I'd like to share about the solution of my problem as below:

For Report 1
In Cell J6:
=LARGE(INDEX(SUMIFS(Sheet1!$E$4:$E$400,Sheet1!$C$4:$C$400,"<>"&$H$16,Sheet1!$C$4:$C$400,"<>"&$H$17,Sheet1!$C$4:$C$400,"<>"&$H$18,Sheet1!$C$4:$C$400,"<>"&$H$19,Sheet1!$A$4:$A$400,">="&$I$3,Sheet1!$A$4:$A$400,"<="&$L$3,Sheet1!$F$4:$F$400,Sheet1!$F$4:$F$400)*(COUNTIF($I$5:$I5,Sheet1!$F$4:$F$400)=0),0),1)
In Cell i6:
=INDEX(Sheet1!$F$4:$F$400,MATCH(J6,INDEX(SUMIFS(Sheet1!$E$4:$E$400,Sheet1!$C$4:$C$400,"<>"&$H$16,Sheet1!$C$4:$C$400,"<>"&$H$17,Sheet1!$C$4:$C$400,"<>"&$H$18,Sheet1!$C$4:$C$400,"<>"&$H$19,Sheet1!$A$4:$A$400,">="&$I$3,Sheet1!$A$4:$A$400,"<="&$L$3,Sheet1!$F$4:$F$400,Sheet1!$F$4:$F$400)*(COUNTIF($J$5:$J5,Sheet1!$F$4:$F$400)=0),0),0))

Cell K6:
=SUM(--(FREQUENCY(IF(Sheet1!$F$4:$F$400=I6,IF(ISERROR(MATCH(Sheet1!$C$4:$C$400,$H$16:$I$19,0)),IF(Sheet1!$A$4:$A$400>=$I$3,IF(Sheet1!$A$4:$A$400<=$L$3,MATCH(Sheet1!$B$4:$B$400,Sheet1!$B$4:$B$400,0))))),ROW(Sheet1!$B$4:$B$400)-ROW($B$4)+1)>0))

Cell L6:
=SUMIFS(Sheet1!$D$4:$D$400,Sheet1!$F$4:$F$400,I6,Sheet1!$C$4:$C$400,"<>"&$H$16,Sheet1!$C$4:$C$400,"<>"&$H$17,Sheet1!$C$4:$C$400,"<>"&$H$18,Sheet1!$C$4:$C$400,"<>"&$H$19,Sheet1!$A$4:$A$400,">="&$I$3,Sheet1!$A$4:$A$400,"<="&$L$3)

For Report 2:
Cell O6:
=SUMIFS(Sheet1!$E$4:$E$400,Sheet1!$A$4:$A$400,[@Date],Sheet1!$C$4:$C$400,"<>"&$H$16,Sheet1!$C$4:$C$400,"<>"&$H$17,Sheet1!$C$4:$C$400,"<>"&$H$18,Sheet1!$C$4:$C$400,"<>"&$H$19)

Cell P6:
=SUM(--(FREQUENCY(IF(ISERROR(MATCH(Sheet1!$C$4:$C$400,$H$16:$I$19,0)),IF(Sheet1!$A$4:$A$400=[@Date],MATCH(Sheet1!$B$4:$B$400,Sheet1!$B$4:$B$400,0))),ROW(Sheet1!$B$4:$B$400)-ROW($B$4)+1)>0))

Cell Q6:
=SUMIFS(Sheet1!$D$4:$D$400,Sheet1!$A$4:$A$400,[@Date],Sheet1!$C$4:$C$400,"<>"&$H$16,Sheet1!$C$4:$C$400,"<>"&$H$17,Sheet1!$C$4:$C$400,"<>"&$H$18,Sheet1!$C$4:$C$400,"<>"&$H$19)

Thank you!