MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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!!