That comes so close but I think I will still run into trouble. What I have is (FC=Fulfillment Center):
A B 1 Supplier #1 2 FC1 Item #1 1.00 3 FC1 Item #2 2.00 4 FC1 Total 3.00 5 FC2 Item #1 4.00 6 FC2 Total 4.00 7 Supp #1 Total 7.00 8 Supplier #2 9 FC1 Item #1 3.00 10 FC1 Item #2 2.00 11 FC1 Total 5.00 12 FC2 Item #1 6.00 13 FC2 Total 6.00 14 Supp #1 Total 11.00 15 Total (All) 18.00 What I end up with is a formula like this in my Grand Total: =SUM(G10:G11,G20:G22...), the sheet has 3 nested forEach's and I fear I'll run into the same issue even with your approach. I think it is because there are text column headers for each section. I may need to figure out a way to tuck the amounts into columns someplace else and get a sum from there but the loops are still creating gaps... Thanks, Jim -----Original Message----- From: Michael Zalewski [mailto:[email protected]] Sent: Tuesday, August 31, 2010 3:13 PM To: [email protected] Subject: Re: Help with SUM function Sounds like you are having a column with Subtotals and Grand Totals. The SUM function that yields your grand total does not need to pick out ranges. Just run the SUM function over the entire column For example A B 1 Supplier #1 1.00 2 2.00 3 Subtotal @SUM(B1:B2) 4 Supplier #2 3.00 5 4.00 6 Subtotal @SUM(B4:B5) 7 GRAND TOTAL @SUM(B1:B5) You would think that the GRAND TOTAL would be double the correct result, because it looks like the formula includes the subtotals at B3 and B6. But such is not the case. The SUM function will ignore cells which contain subtotals from cells already included in the SUM. I'm not sure that the POI Formula Evaluator behaves this way. But Excel does. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
