How to aggregate data based on multiple criteria?

[Jurez]

Hi All

I've a table for inventory tracking where parts coming from oversease needs to be tracked based on estimated monthly arrivals. Parts are spread across multiple order numbers and what I am trying to do is develop a report in which collate the part numbers such a way that for each individual part number, its aggregate total in transit is shown under a calendar month. The delivery schedule is well into 2024. But a few problems I am not able to figure out are that:

(i) Same part number under same order number is being shipped out over multiple shipments, so how do I make an aggregate for these arrivals and put them under separate calendar months according to their ETA?

(ii) The file contains historical data as well i.e parts which have recently been delivered. I am not able to come up with a logic to exclude these delivered parts from the count of incoming inventory.

The source file gets updated maybe couple of times a month and because most of the deliveries are well into the future, projection would not change that much in my report.

I've been trying various combination of vlookup, count, countif, IF statements but unable to come up with any formulae that could achieve the results. Of course a crude way would be to just filter the date based on required parameters, get the figures and put them manually in a calendar based table but I really want to see if some bit of automation can be constructed for this task. Would highly appreciate any assistance.

Cust. Order No.	Qty	Qty In transit	PCB Model	In transit ?	Required ETA
CZ10462	1	0	11T40S4000-3D.007		08-Jun-23
CZ10462	1	0	11T40S4000-3D.007		07-Jun-23
CZ10462	1	0	11T40S4000-3D.007		15-Jun-23
CZ10462	5	0	12T40S5000-5D.003		08-Aug-23
CZ10462	5	0	12T40S5000-5D.003		15-Aug-23
CZ10462	1	1	11T40S4000-3D.007	yes	11-Oct-23
CZ10462	2	2	11T40S4000-3D.007		22-Aug-23
CZ10462	1	0	11T40S4000-3D.007		18-Jul-23
CZ10462	1	0	11T40S4000-3D.007		25-Jul-23
CZ10462	2	2	11T40S4000-3D.007	yes	22-Sep-23
CZ10462	2	2	11T40S4000-3D.007	yes	09-Oct-23
CZ10462	2	2	11T40S4000-3D.007	yes	13-Oct-23
CZ10462	5	5	12T40S5000-5D.003	yes	20-Oct-23
CZ10462	2	2	11T40S4000-3D.007	yes	23-Oct-23
CZ10462	4	4	12T40S5000-5D.003	yes	23-Oct-23
CZ10462	2	2	11T40S4000-3D.007	yes	25-Oct-23
CZ10462	1	0	11T40S4000-3D.007	no	26-Nov-23
CZ10462	1	0	11T40S4000-3D.007	no	26-Nov-23
CZ10462	2	0	11T40S4000-3D.007	no	08-Nov-23
CZ10462	2	0	11T40S4000-3D.007	no	15-Nov-23
CZ10462	2	0	11T40S4000-3D.007	no	20-Dec-23
CZ10462	3	0	13T40S7000-7D.005	no	03-Jan-24
CZ10503	3	0	12T40S5000-5D.003	no	31-Mar-24
CZ10503	2	0	12T40S5000-5D.003	no	31-Mar-24
CZ10503	1	0	12T40S5000-5D.003	no	31-Mar-24
CZ10503	1	0	12T40S5000-5D.003	no	31-Mar-24
CZ100504	3	0	14T40S8000-8D.006	no	08-Apr-24
CZ100504	2	0	14T40S8000-8D.006	no	08-Apr-24
CZ100508	1	0	13T40S7000-7D.005	no	12-Apr-24
CZ100502	2	0	13T40S7000-7D.005	no	12-Apr-24
CZ100502	2	0	13T40S7000-7D.005	no	04-May-24
CZ100504	3	0	14T40S8000-8D.006	no	28-Apr-24
CZ100502	2	0	13T40S7000-7D.005	no	04-May-24
CZ100507	3	0	12T40S5000-5D.003	no	11-May-24
CZ100509	4	0	15T40S9000-9D.007	no	31-May-24
CZ100507	1	0	12T40S5000-5D.003	no	05-Jun-24
CZ100505	2	0	16T40S4000-4D.001	no	15-Jun-24
CZ100505	2	0	16T40S4000-4D.001	no	15-Jun-24
CZ100508	3	0	13T40S7000-7D.005	no	10-Jun-24
CZ100505	4	0	16T40S4000-4D.001	no	06-Jul-24
CZ100507	6	0	12T40S5000-5D.003	no	13-Jul-24
CZ100569	3	0	11T40S4000-3D.007	no	31-Jul-24
CZ100505	2	0	16T40S4000-4D.001	no	03-Aug-24
CZ100550	3	0	12T40S5000-5D.003	no	31-May-24
CZ100506	1	0	17T40S3000-4D.002	no	31-Mar-24
CZ100550	3	0	12T40S5000-5D.003	no	31-May-24
CZ100550	3	0	12T40S5000-5D.003	no	31-May-24
CZ100569	3	0	11T40S4000-3D.007	no	01-Oct-24
CZ100550	2	0	12T40S5000-5D.003	no	31-May-24
CZ100550	3	0	12T40S5000-5D.003	no	31-May-24
CZ100550	2	0	12T40S5000-5D.003	no	31-May-24
CZ100506	1	0	17T40S3000-4D.002	no	31-Mar-24
CZ100502	4	0	13T40S7000-7D.005	no	02-Nov-24
CZ100550	4	0	12T40S5000-5D.003	no	31-May-24
CZ100506	4	0	17T40S3000-4D.002	no	31-Mar-24
CZ100506	1	0	17T40S3000-4D.002	no	31-Mar-24
CZ100563	4	0	17T40S3000-4D.002	no	30-Sep-24
CZ100563	4	0	17T40S3000-4D.002	no	30-Sep-24

 

[kevin9999]

There's a number of ways you could do this, here's just one:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Cust. Order No.	Qty	Qty In transit	PCB Model	In transit ?	Required ETA
2	CZ10462	1	0	11T40S4000-3D.007		8-Jun-23
3	CZ10462	1	0	11T40S4000-3D.007		7-Jun-23
4	CZ10462	1	0	11T40S4000-3D.007		15-Jun-23
5	CZ10462	5	0	12T40S5000-5D.003		8-Aug-23
6	CZ10462	5	0	12T40S5000-5D.003		15-Aug-23
7	CZ10462	1	1	11T40S4000-3D.007	yes	11-Oct-23
8	CZ10462	2	2	11T40S4000-3D.007		22-Aug-23
9	CZ10462	1	0	11T40S4000-3D.007		18-Jul-23
10	CZ10462	1	0	11T40S4000-3D.007		25-Jul-23
11	CZ10462	2	2	11T40S4000-3D.007	yes	22-Sep-23
12	CZ10462	2	2	11T40S4000-3D.007	yes	9-Oct-23
13	CZ10462	2	2	11T40S4000-3D.007	yes	13-Oct-23
14	CZ10462	5	5	12T40S5000-5D.003	yes	20-Oct-23
15	CZ10462	2	2	11T40S4000-3D.007	yes	23-Oct-23
16	CZ10462	4	4	12T40S5000-5D.003	yes	23-Oct-23
17	CZ10462	2	2	11T40S4000-3D.007	yes	25-Oct-23
18	CZ10462	1	0	11T40S4000-3D.007	no	26-Nov-23
19	CZ10462	1	0	11T40S4000-3D.007	no	26-Nov-23
20	CZ10462	2	0	11T40S4000-3D.007	no	8-Nov-23
21	CZ10462	2	0	11T40S4000-3D.007	no	15-Nov-23
22	CZ10462	2	0	11T40S4000-3D.007	no	20-Dec-23
23	CZ10462	3	0	13T40S7000-7D.005	no	3-Jan-24
24	CZ10503	3	0	12T40S5000-5D.003	no	31-Mar-24
25	CZ10503	2	0	12T40S5000-5D.003	no	31-Mar-24
26	CZ10503	1	0	12T40S5000-5D.003	no	31-Mar-24
27	CZ10503	1	0	12T40S5000-5D.003	no	31-Mar-24
28	CZ100504	3	0	14T40S8000-8D.006	no	8-Apr-24
29	CZ100504	2	0	14T40S8000-8D.006	no	8-Apr-24
30	CZ100508	1	0	13T40S7000-7D.005	no	12-Apr-24
31	CZ100502	2	0	13T40S7000-7D.005	no	12-Apr-24
32	CZ100502	2	0	13T40S7000-7D.005	no	4-May-24
33	CZ100504	3	0	14T40S8000-8D.006	no	28-Apr-24
34	CZ100502	2	0	13T40S7000-7D.005	no	4-May-24
35	CZ100507	3	0	12T40S5000-5D.003	no	11-May-24
36	CZ100509	4	0	15T40S9000-9D.007	no	31-May-24
37	CZ100507	1	0	12T40S5000-5D.003	no	5-Jun-24
38	CZ100505	2	0	16T40S4000-4D.001	no	15-Jun-24
39	CZ100505	2	0	16T40S4000-4D.001	no	15-Jun-24
40	CZ100508	3	0	13T40S7000-7D.005	no	10-Jun-24
41	CZ100505	4	0	16T40S4000-4D.001	no	6-Jul-24
42	CZ100507	6	0	12T40S5000-5D.003	no	13-Jul-24
43	CZ100569	3	0	11T40S4000-3D.007	no	31-Jul-24
44	CZ100505	2	0	16T40S4000-4D.001	no	3-Aug-24
45	CZ100550	3	0	12T40S5000-5D.003	no	31-May-24
46	CZ100506	1	0	17T40S3000-4D.002	no	31-Mar-24
47	CZ100550	3	0	12T40S5000-5D.003	no	31-May-24
48	CZ100550	3	0	12T40S5000-5D.003	no	31-May-24
49	CZ100569	3	0	11T40S4000-3D.007	no	1-Oct-24
50	CZ100550	2	0	12T40S5000-5D.003	no	31-May-24
51	CZ100550	3	0	12T40S5000-5D.003	no	31-May-24
52	CZ100550	2	0	12T40S5000-5D.003	no	31-May-24
53	CZ100506	1	0	17T40S3000-4D.002	no	31-Mar-24
54	CZ100502	4	0	13T40S7000-7D.005	no	2-Nov-24
55	CZ100550	4	0	12T40S5000-5D.003	no	31-May-24
56	CZ100506	4	0	17T40S3000-4D.002	no	31-Mar-24
57	CZ100506	1	0	17T40S3000-4D.002	no	31-Mar-24
58	CZ100563	4	0	17T40S3000-4D.002	no	30-Sep-24
59	CZ100563	4	0	17T40S3000-4D.002	no	30-Sep-24
60
61
62	Part	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24
63	11T40S4000-3D.007	3	2	2	2	9	6	2							3			3
64	12T40S5000-5D.003			10		9					7		23	1	6
65	13T40S7000-7D.005								3			3	4	3
66	14T40S8000-8D.006											8
67	15T40S9000-9D.007												4
68	16T40S4000-4D.001													4	4	2
69	17T40S3000-4D.002										7						8
Sheet1

Cell Formulas
Range	Formula
A63:A69	A63	=UNIQUE(D2:D59)
B63:R69	B63	=IFERROR(SUM(FILTER($B$2:$B$59,($D$2:$D$59=$A63)*(TEXT($F$2:$F$59,"mmm-yy")=TEXT(B$62,"mmm-yy")),"")),"")
Dynamic array formulas.

 

[RoryA]

Welcome to the MrExcel Message Board!

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 aggregate data based on multiple criteria?

Hi All I've a table for inventory tracking where parts coming from oversease needs to be tracked based on estimated monthly arrivals. Parts are spread across multiple order numbers and what I am trying to do is develop a report in which collate the part numbers such a way that for each...

www.excelforum.com

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.

 

[Jurez]

Welcome to the MrExcel Message Board!

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 aggregate data based on multiple criteria?

Hi All I've a table for inventory tracking where parts coming from oversease needs to be tracked based on estimated monthly arrivals. Parts are spread across multiple order numbers and what I am trying to do is develop a report in which collate the part numbers such a way that for each...

www.excelforum.com

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.

I apologize for any inconvenience and unintended violation of forum rules and will be careful about it going forward.

 

[Jurez]

Hi Kevin

Thanks a lot for taking time out to reply to my query. I am at the very beginner level of Excel use. And while I understand the first part of solution [=UNIQUE(D2:D59)] filters out duplicates, I wasn't able to fully understand the logic of the second part. That's a lot more nested statement, if it's not too much, could you please briefly explain the working of this second part. And when I put this formula in cell B63, it did not give any result. Can you please guide me on how to make it work.

 

[kevin9999]

please briefly explain the working of this second part

Hi Jurez,

I’ll try
I personally find it easiest when trying to unravel a compound formula to start from the centre & work my way to the outside.

=IFERROR(SUM(FILTER($B$2:$B$59,($D$2:$D$59=$A63)*(TEXT($F$2:$F$59,"mmm-yy")=TEXT(B$62,"mmm-yy")),"")),"")

The inner most function is the TEXT() function. It works like this: TEXT(<text_to_format>,<desired_format>) and in this usage is making sure both dates (the dates in column F and the month of interest in row 62) are in the same format (“mmm-yy”) to expedite comparison.
More here: TEXT function - Microsoft Support

This is wrapped in a FILTER() function, which follows the pattern: FILTER(<range_to_filter>,<condition(s)_to_filter_by>,<if_empty_return_this>). In this case more than one matching condition is being assessed (part number and date) and the 2 conditions within the FILTER function are separated with an asterisk. Here it is saying filter the range column B ((<range_to_filter>) if the part number in column D matches the part number in column A of the table starting in row 63 and if the dates in column F match the date in row 62 (<condition_to_filter_by>) and return a blank “” if nothing found (<if_empty_return_this>) that matches the condition.
More here: FILTER function - Microsoft Support

This is wrapped in a SUM() function – fairly self-explanatory in that it sums all the values that match the FILTER() condition.

Finally wrapped in an IFERROR() function, with follows the pattern IFERROR(<function>,<return_this_if_the_function_returns_an_error)
More here: IFERROR function - Microsoft Support

In plain English, the function is saying: add all the values in column F, if the part number in column D matches the part number in column A of the summary table and the dates on that row matches the date in row 62 (to get a month summary for that part number) and if nothing found that matches return an empty string (“”) and also return an empty string if the formula errors.
I have no idea why the formula is not working for you. It would help if you could provide a copy of your sheet using the XL2BB add in or alternatively, share your file via Google Drive, Dropbox or similar file sharing platform.

Hope this helps.

 

[Jurez]

Hi Jurez,

I’ll try
I personally find it easiest when trying to unravel a compound formula to start from the centre & work my way to the outside.

=IFERROR(SUM(FILTER($B$2:$B$59,($D$2:$D$59=$A63)*(TEXT($F$2:$F$59,"mmm-yy")=TEXT(B$62,"mmm-yy")),"")),"")

The inner most function is the TEXT() function. It works like this: TEXT(<text_to_format>,<desired_format>) and in this usage is making sure both dates (the dates in column F and the month of interest in row 62) are in the same format (“mmm-yy”) to expedite comparison.
More here: TEXT function - Microsoft Support

This is wrapped in a FILTER() function, which follows the pattern: FILTER(<range_to_filter>,<condition(s)_to_filter_by>,<if_empty_return_this>). In this case more than one matching condition is being assessed (part number and date) and the 2 conditions within the FILTER function are separated with an asterisk. Here it is saying filter the range column B ((<range_to_filter>) if the part number in column D matches the part number in column A of the table starting in row 63 and if the dates in column F match the date in row 62 (<condition_to_filter_by>) and return a blank “” if nothing found (<if_empty_return_this>) that matches the condition.
More here: FILTER function - Microsoft Support

This is wrapped in a SUM() function – fairly self-explanatory in that it sums all the values that match the FILTER() condition.

Finally wrapped in an IFERROR() function, with follows the pattern IFERROR(<function>,<return_this_if_the_function_returns_an_error)
More here: IFERROR function - Microsoft Support

In plain English, the function is saying: add all the values in column F, if the part number in column D matches the part number in column A of the summary table and the dates on that row matches the date in row 62 (to get a month summary for that part number) and if nothing found that matches return an empty string (“”) and also return an empty string if the formula errors.
I have no idea why the formula is not working for you. It would help if you could provide a copy of your sheet using the XL2BB add in or alternatively, share your file via Google Drive, Dropbox or similar file sharing platform.

Hope this helps.

Thank you so much for the detailed explanation. It really helps to build my Excel base

 

[kevin9999]

Happy to help & thanks for the feedback

 

[Jurez]

I have no idea why the formula is not working for you. It would help if you could provide a copy of your sheet using the XL2BB add in or alternatively, share your file via Google Drive, Dropbox or similar file sharing platform.

Hope this helps.

I was able to fix it as earlier I had not added the calendar month-year in Row 62. Now it is showing the all the values. Thanks again for the wonderful solution. You've been a great help.