How to aggregate data based on multiple criteria?

J

Jurez

New Member
Joined
Sep 28, 2023
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.QtyQty In transitPCB ModelIn transit ?Required ETA
CZ104621011T40S4000-3D.00708-Jun-23
CZ104621011T40S4000-3D.00707-Jun-23
CZ104621011T40S4000-3D.00715-Jun-23
CZ104625012T40S5000-5D.00308-Aug-23
CZ104625012T40S5000-5D.00315-Aug-23
CZ104621111T40S4000-3D.007yes11-Oct-23
CZ104622211T40S4000-3D.00722-Aug-23
CZ104621011T40S4000-3D.00718-Jul-23
CZ104621011T40S4000-3D.00725-Jul-23
CZ104622211T40S4000-3D.007yes22-Sep-23
CZ104622211T40S4000-3D.007yes09-Oct-23
CZ104622211T40S4000-3D.007yes13-Oct-23
CZ104625512T40S5000-5D.003yes20-Oct-23
CZ104622211T40S4000-3D.007yes23-Oct-23
CZ104624412T40S5000-5D.003yes23-Oct-23
CZ104622211T40S4000-3D.007yes25-Oct-23
CZ104621011T40S4000-3D.007no26-Nov-23
CZ104621011T40S4000-3D.007no26-Nov-23
CZ104622011T40S4000-3D.007no08-Nov-23
CZ104622011T40S4000-3D.007no15-Nov-23
CZ104622011T40S4000-3D.007no20-Dec-23
CZ104623013T40S7000-7D.005no03-Jan-24
CZ105033012T40S5000-5D.003no31-Mar-24
CZ105032012T40S5000-5D.003no31-Mar-24
CZ105031012T40S5000-5D.003no31-Mar-24
CZ105031012T40S5000-5D.003no31-Mar-24
CZ1005043014T40S8000-8D.006no08-Apr-24
CZ1005042014T40S8000-8D.006no08-Apr-24
CZ1005081013T40S7000-7D.005no12-Apr-24
CZ1005022013T40S7000-7D.005no12-Apr-24
CZ1005022013T40S7000-7D.005no04-May-24
CZ1005043014T40S8000-8D.006no28-Apr-24
CZ1005022013T40S7000-7D.005no04-May-24
CZ1005073012T40S5000-5D.003no11-May-24
CZ1005094015T40S9000-9D.007no31-May-24
CZ1005071012T40S5000-5D.003no05-Jun-24
CZ1005052016T40S4000-4D.001no15-Jun-24
CZ1005052016T40S4000-4D.001no15-Jun-24
CZ1005083013T40S7000-7D.005no10-Jun-24
CZ1005054016T40S4000-4D.001no06-Jul-24
CZ1005076012T40S5000-5D.003no13-Jul-24
CZ1005693011T40S4000-3D.007no31-Jul-24
CZ1005052016T40S4000-4D.001no03-Aug-24
CZ1005503012T40S5000-5D.003no31-May-24
CZ1005061017T40S3000-4D.002no31-Mar-24
CZ1005503012T40S5000-5D.003no31-May-24
CZ1005503012T40S5000-5D.003no31-May-24
CZ1005693011T40S4000-3D.007no01-Oct-24
CZ1005502012T40S5000-5D.003no31-May-24
CZ1005503012T40S5000-5D.003no31-May-24
CZ1005502012T40S5000-5D.003no31-May-24
CZ1005061017T40S3000-4D.002no31-Mar-24
CZ1005024013T40S7000-7D.005no02-Nov-24
CZ1005504012T40S5000-5D.003no31-May-24
CZ1005064017T40S3000-4D.002no31-Mar-24
CZ1005061017T40S3000-4D.002no31-Mar-24
CZ1005634017T40S3000-4D.002no30-Sep-24
CZ1005634017T40S3000-4D.002no30-Sep-24
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
There's a number of ways you could do this, here's just one:
Book1
ABCDEFGHIJKLMNOPQR
1Cust. Order No.QtyQty In transitPCB ModelIn transit ?Required ETA
2CZ104621011T40S4000-3D.0078-Jun-23
3CZ104621011T40S4000-3D.0077-Jun-23
4CZ104621011T40S4000-3D.00715-Jun-23
5CZ104625012T40S5000-5D.0038-Aug-23
6CZ104625012T40S5000-5D.00315-Aug-23
7CZ104621111T40S4000-3D.007yes11-Oct-23
8CZ104622211T40S4000-3D.00722-Aug-23
9CZ104621011T40S4000-3D.00718-Jul-23
10CZ104621011T40S4000-3D.00725-Jul-23
11CZ104622211T40S4000-3D.007yes22-Sep-23
12CZ104622211T40S4000-3D.007yes9-Oct-23
13CZ104622211T40S4000-3D.007yes13-Oct-23
14CZ104625512T40S5000-5D.003yes20-Oct-23
15CZ104622211T40S4000-3D.007yes23-Oct-23
16CZ104624412T40S5000-5D.003yes23-Oct-23
17CZ104622211T40S4000-3D.007yes25-Oct-23
18CZ104621011T40S4000-3D.007no26-Nov-23
19CZ104621011T40S4000-3D.007no26-Nov-23
20CZ104622011T40S4000-3D.007no8-Nov-23
21CZ104622011T40S4000-3D.007no15-Nov-23
22CZ104622011T40S4000-3D.007no20-Dec-23
23CZ104623013T40S7000-7D.005no3-Jan-24
24CZ105033012T40S5000-5D.003no31-Mar-24
25CZ105032012T40S5000-5D.003no31-Mar-24
26CZ105031012T40S5000-5D.003no31-Mar-24
27CZ105031012T40S5000-5D.003no31-Mar-24
28CZ1005043014T40S8000-8D.006no8-Apr-24
29CZ1005042014T40S8000-8D.006no8-Apr-24
30CZ1005081013T40S7000-7D.005no12-Apr-24
31CZ1005022013T40S7000-7D.005no12-Apr-24
32CZ1005022013T40S7000-7D.005no4-May-24
33CZ1005043014T40S8000-8D.006no28-Apr-24
34CZ1005022013T40S7000-7D.005no4-May-24
35CZ1005073012T40S5000-5D.003no11-May-24
36CZ1005094015T40S9000-9D.007no31-May-24
37CZ1005071012T40S5000-5D.003no5-Jun-24
38CZ1005052016T40S4000-4D.001no15-Jun-24
39CZ1005052016T40S4000-4D.001no15-Jun-24
40CZ1005083013T40S7000-7D.005no10-Jun-24
41CZ1005054016T40S4000-4D.001no6-Jul-24
42CZ1005076012T40S5000-5D.003no13-Jul-24
43CZ1005693011T40S4000-3D.007no31-Jul-24
44CZ1005052016T40S4000-4D.001no3-Aug-24
45CZ1005503012T40S5000-5D.003no31-May-24
46CZ1005061017T40S3000-4D.002no31-Mar-24
47CZ1005503012T40S5000-5D.003no31-May-24
48CZ1005503012T40S5000-5D.003no31-May-24
49CZ1005693011T40S4000-3D.007no1-Oct-24
50CZ1005502012T40S5000-5D.003no31-May-24
51CZ1005503012T40S5000-5D.003no31-May-24
52CZ1005502012T40S5000-5D.003no31-May-24
53CZ1005061017T40S3000-4D.002no31-Mar-24
54CZ1005024013T40S7000-7D.005no2-Nov-24
55CZ1005504012T40S5000-5D.003no31-May-24
56CZ1005064017T40S3000-4D.002no31-Mar-24
57CZ1005061017T40S3000-4D.002no31-Mar-24
58CZ1005634017T40S3000-4D.002no30-Sep-24
59CZ1005634017T40S3000-4D.002no30-Sep-24
60
61
62PartJun-23Jul-23Aug-23Sep-23Oct-23Nov-23Dec-23Jan-24Feb-24Mar-24Apr-24May-24Jun-24Jul-24Aug-24Sep-24Oct-24
6311T40S4000-3D.0073222962      3  3
6412T40S5000-5D.003  10 9    7 2316   
6513T40S7000-7D.005       3  343    
6614T40S8000-8D.006          8      
6715T40S9000-9D.007           4     
6816T40S4000-4D.001            442  
6917T40S3000-4D.002         7     8 
Sheet1
Cell Formulas
RangeFormula
A63:A69A63=UNIQUE(D2:D59)
B63:R69B63=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.
 
Upvote 0
Solution
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.
 
Upvote 0
RoryA said:
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.
Click to expand...
I apologize for any inconvenience and unintended violation of forum rules and will be careful about it going forward.
 
Upvote 0
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.
 
Upvote 0
Jurez said:
please briefly explain the working of this second part
Click to expand...
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.
 
Upvote 0
kevin9999 said:
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.
Click to expand...
Thank you so much for the detailed explanation. It really helps to build my Excel base :)
 
Upvote 0
kevin9999 said:
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.
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
lookup return from range of dates
Replies
4
Views
120
Peter_SSs
Peter_SSs
Dexir
  • Solved
Countifs query
Replies
3
Views
186
Dexir
Dexir
J
  • Question
adding cells based on criteria across several columns
Replies
5
Views
88
Cubist
Cubist
V
  • Solved
Week Number
Replies
2
Views
121
vmjan02
V
P
  • Locked
  • Question
Formula to build table of months
Replies
1
Views
133
Fluff
Fluff

Forum statistics

Threads
1,215,094
Messages
6,123,071
Members
449,092
Latest member
ipruravindra

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top