# 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.