mirrordude
New Member
- Joined
- Apr 11, 2012
- Messages
- 2
Hi! I searched for a couple of hours for how to do this, but can't find anything. I'm using Excel 2007.
I've got a 20,000 row spreadsheet that tracks shipping information. Each row contains the Origin company and city, and Destination company and city, as well as the shipment date, shipment cost, number of boxes, and weight. I've got autofilter activated so if, for example, I want to see only the shipments from Company A in Orlando to Company B in Tampa in January 2011, it will filter the results and show me only the 63 relevant rows for those shipments.
At the bottom of the spreadsheet, I use the subtotal function to add up the number of shipments, the number of boxes, the total weight, and the total cost. I use the subtotal function instead of the sum function because it ignores the cells not included from the filter. This works great.
Now, in a second sheet in the same workbook I separated out our busiest 50 origin-destination pairs onto separate rows. I want to add the total number of shipments, total weight, etc. into these rows. I started doing it by manually applying filters to the first sheet, writing down the totals, and then adding them into the second sheet. I then stopped and thought there must be a way to automate this. I could use a pivot table to do the totals, but (a) I'm going to do a bunch of calculations to the results and then apply conditional formatting to them, and (B) I need to export the results in a comma delimited format to a mapping program later, so I'd prefer to do this using formulas.
Right now, my subtotal formula looks like:
=SUBTOTAL(9,'Sheet1'n:n)
which returns the total number of boxes shipped, but only after the filters on Sheet1 are manually applied for the origin-destination pair that I want. I would like to have a formula that gives me the subtotal result for the cities I specify in Sheet2 applied as filters on Sheet 1 in one neat formula, which I would envision (in my newbness) as looking something like this as a formula in cell C1 of Sheet2:
=SUBTOTAL(9,'Sheet1'n:n,'Sheet1'a:a.filter=A1,'Sheet1'b:b.filter=B1)
where A1 (in Sheet2) is Orlando, B2 is Tampa, and I get the result I want. Then I can have multiple rows in Sheet2 with different city pairs in columns A and B and I can copy the formula down column C and get the total number of boxes shipped for each O-D pair, and can easily export my data. I can also add data to Sheet 1 every month as I get new reports and my calculations in Sheet 2 will update.
Does this make sense? Is it possible to specify a filter to be applied to a cell range reference inside a subtotal formula?
I've got a 20,000 row spreadsheet that tracks shipping information. Each row contains the Origin company and city, and Destination company and city, as well as the shipment date, shipment cost, number of boxes, and weight. I've got autofilter activated so if, for example, I want to see only the shipments from Company A in Orlando to Company B in Tampa in January 2011, it will filter the results and show me only the 63 relevant rows for those shipments.
At the bottom of the spreadsheet, I use the subtotal function to add up the number of shipments, the number of boxes, the total weight, and the total cost. I use the subtotal function instead of the sum function because it ignores the cells not included from the filter. This works great.
Now, in a second sheet in the same workbook I separated out our busiest 50 origin-destination pairs onto separate rows. I want to add the total number of shipments, total weight, etc. into these rows. I started doing it by manually applying filters to the first sheet, writing down the totals, and then adding them into the second sheet. I then stopped and thought there must be a way to automate this. I could use a pivot table to do the totals, but (a) I'm going to do a bunch of calculations to the results and then apply conditional formatting to them, and (B) I need to export the results in a comma delimited format to a mapping program later, so I'd prefer to do this using formulas.
Right now, my subtotal formula looks like:
=SUBTOTAL(9,'Sheet1'n:n)
which returns the total number of boxes shipped, but only after the filters on Sheet1 are manually applied for the origin-destination pair that I want. I would like to have a formula that gives me the subtotal result for the cities I specify in Sheet2 applied as filters on Sheet 1 in one neat formula, which I would envision (in my newbness) as looking something like this as a formula in cell C1 of Sheet2:
=SUBTOTAL(9,'Sheet1'n:n,'Sheet1'a:a.filter=A1,'Sheet1'b:b.filter=B1)
where A1 (in Sheet2) is Orlando, B2 is Tampa, and I get the result I want. Then I can have multiple rows in Sheet2 with different city pairs in columns A and B and I can copy the formula down column C and get the total number of boxes shipped for each O-D pair, and can easily export my data. I can also add data to Sheet 1 every month as I get new reports and my calculations in Sheet 2 will update.
Does this make sense? Is it possible to specify a filter to be applied to a cell range reference inside a subtotal formula?
