Formula
Posted by Mike on February 11, 2002 2:11 PM
I have three columns with the following data elements in them: Column A has names, such as Fabrication, Group Offices, Plant A, Plant B, Admiministrative. Column B has an entry for either To or From. Column C has various numbers in them, such as -123,221, -5,574,263, 5,112,324, 25,478, etc. I am trying to segregate the sums of column C based upon what the data holds in Columns A & B. For example, I want the sums for all of the entries that have Fabrication and From in them, or Plant A and To, or Group Offices and From. Can anyone help?
Posted by DRJ on February 11, 2002 2:21 PM
Sounds like you need to use excels filter or subtotals functions, they should do the trick.
DRJ
Posted by Mike on February 11, 2002 2:23 PM
Can you give an example?
MJ
Posted by Mike on February 11, 2002 2:36 PM
Posted by Aladin Akyurek on February 11, 2002 2:38 PM
Mike --
You should definitely have a look at PivotTables.
What follows is a formula-based approach.
I'll assume that your data start at row 2 (row 1 has presumably appropriate labels corresponding to data underneath).
In D1 enter: =MATCH(9.99999999999999E+307,C:C)
In E1 enter: From
In F1 enter: To
Make a unique list of items of interest (from column A of your data like Fabrication, Plant A, etc.) from D2 on. You can use Advanced Filter to create this list or do it manually.
In E2 enter: =SUMPRODUCT((OFFSET($A$2,0,0,$D$1,1)=$D2)*(OFFSET($B$2,0,0,$D$1,1)=E$1),(OFFSET($C$2,0,0,$D$1,1))
Copy this first to F2 then down as far as needed.
Aladin
========
Posted by Mike on February 11, 2002 3:12 PM
Aladin--
Thank you. It looks as if it worked. I'll check the numbers tomorrow. This is great!!
