Sum a table of columns & rows

I have a spreadsheet of 154 Rows (all unique project numbers in numerical 
order) and 9 columns of account numbers (some are similiar and some are user 
entered, therefore there could be 'blanks' with no data in them).  

I am trying to create a table that will only give me the project number if 
there are dollars in one or more of the columns.  This would be used for data 
entry (and that is why I would like to have the columns summed up - to remove 
duplicates).

Any ideas?  I have given a brief example below:
                                                                 F, G, & H 
are User Defined Cols
     A                B          C         D         E      F           G    
       H   
1   Project #    1100   1111    1100    1100  "Blank"   4233     1111
2   00001       $50.00  $25.00  $3.00   $1.00              $25.00  $10.00
3   10000                    $5.00                                     $ 
5.00  $10.00
4   25000      $50.00   $10.00                                    $1.00   

What I would like to see is a table of the summarized data below:

Project   Account    Amount
00001     1100       $54.00
00001     1111       $35.00
00001     4233       $25.00
10000     1111       $15.00
10000     4233       $  5.00
25000     1100       $50.00
25000     1111       $10.00
25000     4233       $ 1.00            

Is this something that I can create using a pivot table and if so, how?

Thanks for your help!


0
11/20/2006 4:21:02 PM
excel 39879 articles. 2 followers. Follow

4 Replies
690 Views

Similar Articles

[PageSpeed] 38

try using sumproduct idea
=sumproduct((a2:a200="0001")*(b2:b200=1100)*c2:c200)

-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Frank Costa" <FrankCosta@discussions.microsoft.com> wrote in message 
news:03BAE23F-3998-4188-AB77-609DB80607AE@microsoft.com...
>I have a spreadsheet of 154 Rows (all unique project numbers in numerical
> order) and 9 columns of account numbers (some are similiar and some are 
> user
> entered, therefore there could be 'blanks' with no data in them).
>
> I am trying to create a table that will only give me the project number if
> there are dollars in one or more of the columns.  This would be used for 
> data
> entry (and that is why I would like to have the columns summed up - to 
> remove
> duplicates).
>
> Any ideas?  I have given a brief example below:
>                                                                 F, G, & H
> are User Defined Cols
>     A                B          C         D         E      F           G
>       H
> 1   Project #    1100   1111    1100    1100  "Blank"   4233     1111
> 2   00001       $50.00  $25.00  $3.00   $1.00              $25.00  $10.00
> 3   10000                    $5.00                                     $
> 5.00  $10.00
> 4   25000      $50.00   $10.00                                    $1.00
>
> What I would like to see is a table of the summarized data below:
>
> Project   Account    Amount
> 00001     1100       $54.00
> 00001     1111       $35.00
> 00001     4233       $25.00
> 10000     1111       $15.00
> 10000     4233       $  5.00
> 25000     1100       $50.00
> 25000     1111       $10.00
> 25000     4233       $ 1.00
>
> Is this something that I can create using a pivot table and if so, how?
>
> Thanks for your help!
>
> 


0
dguillett1 (2487)
11/20/2006 5:19:52 PM
Don, thanks for your response, but it confuses me.

In my example, I do have some account numbers that will be the same, so I 
can identify them.  My issue comes in where there are five user defined 
account numbers (which may be the same as the hard coded numbers).  Those 
user defined account numbers are entered into a column and my table picks 
them up from a formula such as "=c4" (where c4 is the cell the user enters 
the account number).  

Does that help clarify my original question?

Thanks for your help.  I do appreciate it.

"Don Guillett" wrote:

> try using sumproduct idea
> =sumproduct((a2:a200="0001")*(b2:b200=1100)*c2:c200)
> 
> -- 
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "Frank Costa" <FrankCosta@discussions.microsoft.com> wrote in message 
> news:03BAE23F-3998-4188-AB77-609DB80607AE@microsoft.com...
> >I have a spreadsheet of 154 Rows (all unique project numbers in numerical
> > order) and 9 columns of account numbers (some are similiar and some are 
> > user
> > entered, therefore there could be 'blanks' with no data in them).
> >
> > I am trying to create a table that will only give me the project number if
> > there are dollars in one or more of the columns.  This would be used for 
> > data
> > entry (and that is why I would like to have the columns summed up - to 
> > remove
> > duplicates).
> >
> > Any ideas?  I have given a brief example below:
> >                                                                 F, G, & H
> > are User Defined Cols
> >     A                B          C         D         E      F           G
> >       H
> > 1   Project #    1100   1111    1100    1100  "Blank"   4233     1111
> > 2   00001       $50.00  $25.00  $3.00   $1.00              $25.00  $10.00
> > 3   10000                    $5.00                                     $
> > 5.00  $10.00
> > 4   25000      $50.00   $10.00                                    $1.00
> >
> > What I would like to see is a table of the summarized data below:
> >
> > Project   Account    Amount
> > 00001     1100       $54.00
> > 00001     1111       $35.00
> > 00001     4233       $25.00
> > 10000     1111       $15.00
> > 10000     4233       $  5.00
> > 25000     1100       $50.00
> > 25000     1111       $10.00
> > 25000     4233       $ 1.00
> >
> > Is this something that I can create using a pivot table and if so, how?
> >
> > Thanks for your help!
> >
> > 
> 
> 
> 
0
11/20/2006 8:07:01 PM
Frank,

Try this macro.  If anyone else could tell me a way to do away with the
LETTERS, then I would be appreciative.  I'm just getting back to Excel
after a few years without it.

Public Sub TotalsOfPivotTable()
    Dim wsin As Worksheet, wsout As Worksheet
    Dim iProjLoop As Integer, iAccoLoop As Integer, x As Integer
    Dim dValue As Double
    Dim proj As String, acco As String
    Dim bFound As Boolean

    Const LETTERS = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

    Set wsin = Sheets("yourSheetNameHere")
    Set wsout = Sheets.Add
    With wsout
        .Name = "Output"
        .Range("A1").Value = "Project"
        .Range("B1").Value = "Account"
        .Range("C1").Value = "Amount"
    End With

    Application.ScreenUpdating = False
    wsin.Select
    For iProjLoop = 2 To ActiveSheet.UsedRange.Rows.Count
        proj = Range("A" & iProjLoop).Value
        For iAccoLoop = 2 To ActiveSheet.UsedRange.Columns.Count
            acco = Range(Mid(LETTERS, iAccoLoop, 1) & "1").Value
            If Not acco = "" Then
                dValue = Range(Mid(LETTERS, iAccoLoop, 1) &
iProjLoop).Value
                If Not dValue = 0 Then
                    wsout.Select
                    bFound = False
                    For x = 2 To ActiveSheet.UsedRange.Rows.Count
                        If Range("A" & x).Value = proj And Range("B" &
x).Value = acco Then
                            Range("C" & x).Value = Range("C" & x).Value
+ dValue
                            bFound = True
                        End If
                    Next x
                    If Not bFound Then
                        x = ActiveSheet.UsedRange.Rows.Count + 1
                        Range("A" & x).Value = proj
                        Range("B" & x).Value = acco
                        Range("C" & x).Value = dValue
                    End If
                    wsin.Select
                End If
            End If
        Next iAccoLoop
    Next iProjLoop
    Application.ScreenUpdating = True
End Sub


Frank Costa wrote:
> Don, thanks for your response, but it confuses me.
>
> In my example, I do have some account numbers that will be the same, so I
> can identify them.  My issue comes in where there are five user defined
> account numbers (which may be the same as the hard coded numbers).  Those
> user defined account numbers are entered into a column and my table picks
> them up from a formula such as "=c4" (where c4 is the cell the user enters
> the account number).
>
> Does that help clarify my original question?
>
> Thanks for your help.  I do appreciate it.
>
> "Don Guillett" wrote:
>
> > try using sumproduct idea
> > =sumproduct((a2:a200="0001")*(b2:b200=1100)*c2:c200)
> >
> > --
> > Don Guillett
> > SalesAid Software
> > dguillett1@austin.rr.com
> > "Frank Costa" <FrankCosta@discussions.microsoft.com> wrote in message
> > news:03BAE23F-3998-4188-AB77-609DB80607AE@microsoft.com...
> > >I have a spreadsheet of 154 Rows (all unique project numbers in numerical
> > > order) and 9 columns of account numbers (some are similiar and some are
> > > user
> > > entered, therefore there could be 'blanks' with no data in them).
> > >
> > > I am trying to create a table that will only give me the project number if
> > > there are dollars in one or more of the columns.  This would be used for
> > > data
> > > entry (and that is why I would like to have the columns summed up - to
> > > remove
> > > duplicates).
> > >
> > > Any ideas?  I have given a brief example below:
> > >                                                                 F, G, & H
> > > are User Defined Cols
> > >     A                B          C         D         E      F           G
> > >       H
> > > 1   Project #    1100   1111    1100    1100  "Blank"   4233     1111
> > > 2   00001       $50.00  $25.00  $3.00   $1.00              $25.00  $10.00
> > > 3   10000                    $5.00                                     $
> > > 5.00  $10.00
> > > 4   25000      $50.00   $10.00                                    $1.00
> > >
> > > What I would like to see is a table of the summarized data below:
> > >
> > > Project   Account    Amount
> > > 00001     1100       $54.00
> > > 00001     1111       $35.00
> > > 00001     4233       $25.00
> > > 10000     1111       $15.00
> > > 10000     4233       $  5.00
> > > 25000     1100       $50.00
> > > 25000     1111       $10.00
> > > 25000     4233       $ 1.00
> > >
> > > Is this something that I can create using a pivot table and if so, how?
> > >
> > > Thanks for your help!
> > >
> > > 
> > 
> > 
> >

0
jlepack (32)
11/20/2006 9:09:00 PM
Then just change to c4
sumproduct((a2:a200="0001")*(b2:b200=c4)*c2:c200)

-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Frank Costa" <FrankCosta@discussions.microsoft.com> wrote in message 
news:2900A734-B08A-426B-B310-12C84F458C67@microsoft.com...
> Don, thanks for your response, but it confuses me.
>
> In my example, I do have some account numbers that will be the same, so I
> can identify them.  My issue comes in where there are five user defined
> account numbers (which may be the same as the hard coded numbers).  Those
> user defined account numbers are entered into a column and my table picks
> them up from a formula such as "=c4" (where c4 is the cell the user enters
> the account number).
>
> Does that help clarify my original question?
>
> Thanks for your help.  I do appreciate it.
>
> "Don Guillett" wrote:
>
>> try using sumproduct idea
>> =>>
>> -- 
>> Don Guillett
>> SalesAid Software
>> dguillett1@austin.rr.com
>> "Frank Costa" <FrankCosta@discussions.microsoft.com> wrote in message
>> news:03BAE23F-3998-4188-AB77-609DB80607AE@microsoft.com...
>> >I have a spreadsheet of 154 Rows (all unique project numbers in 
>> >numerical
>> > order) and 9 columns of account numbers (some are similiar and some are
>> > user
>> > entered, therefore there could be 'blanks' with no data in them).
>> >
>> > I am trying to create a table that will only give me the project number 
>> > if
>> > there are dollars in one or more of the columns.  This would be used 
>> > for
>> > data
>> > entry (and that is why I would like to have the columns summed up - to
>> > remove
>> > duplicates).
>> >
>> > Any ideas?  I have given a brief example below:
>> >                                                                 F, G, & 
>> > H
>> > are User Defined Cols
>> >     A                B          C         D         E      F 
>> > G
>> >       H
>> > 1   Project #    1100   1111    1100    1100  "Blank"   4233     1111
>> > 2   00001       $50.00  $25.00  $3.00   $1.00              $25.00 
>> > $10.00
>> > 3   10000                    $5.00 
>> > $
>> > 5.00  $10.00
>> > 4   25000      $50.00   $10.00                                    $1.00
>> >
>> > What I would like to see is a table of the summarized data below:
>> >
>> > Project   Account    Amount
>> > 00001     1100       $54.00
>> > 00001     1111       $35.00
>> > 00001     4233       $25.00
>> > 10000     1111       $15.00
>> > 10000     4233       $  5.00
>> > 25000     1100       $50.00
>> > 25000     1111       $10.00
>> > 25000     4233       $ 1.00
>> >
>> > Is this something that I can create using a pivot table and if so, how?
>> >
>> > Thanks for your help!
>> >
>> >
>>
>>
>> 


0
dguillett1 (2487)
11/20/2006 10:34:53 PM
Reply:

Similar Artilces:

show columns on other worksheet without using formulas
Hi, I have 1 worksheet containing lot's records. Just like in access, each line is 1 record, and each column contains data. ex: WORKSHEET 1: name title address city 1 dfjw kjhk kjhkj kjhk 2 lmkj lkjlk lkjkuf guyg 3 drdtg xcx yjutuy hgyy Now I want to have a selection of columns on worksheet 2: ex, only name and title: WORKSHEET 2: name title 1 dfjw kjhk 2 lmkj lkjlk 3 drdtg xcx I will only add/modify rows in worksheet 1. And I want that this is filled automaticaly in worksheet...

Pivot table, calculating % difference of subtotal rows
This is what I need to do: I have rows of data in a pivot table that have subtotals associated with them (automatically generated via the pivot table). I need to to take the subtotals for 2009 and 2010 and calculate the % difference. Here's a screenshot of what I'm talking about: http://home.comcast.net/~wilsoch/PercentDiff.jpg Is this even possible or am I going to have to do this manually? Hi I have'nt seen the screenshot as am at work , go to Pivot Table>Formulas>Calculated Field wilsoch@comcast.net wrote: >This is what I need to do: I have rows of ...

Calculated Field in form to table
Can you take a calculated field from a form and pull it in to a table?? ...

Subtract several rows from a total
This is a multi-part message in MIME format. ------=_NextPart_000_0029_01C53AC8.AD6EB0F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi Guys, I have a total in cell C32. I want to subtract numbers in cells C 34-40 = and have the answer in cell C41. I know that =3DC32-C34 will give me an = answer but I want several rows to be subtracted. What would my formula = be? I have used the insert function feature before but I don't see diff or = anything that looks like a subtraction function in there. =20 Thanks, Linda ------=_NextPa...

Deleting rows containing common data
I have an Excel 2000 spreadsheet with 30,000 rows. The rows look like this: 007-007 68-4611 68-4682 total 007-007 total 007-007 28-4831 68-2454 68-4682 total 007-007 28-4831 68-2454 68-4682 total 007-007 28-4831 68-2454 68-4682 total 007-007 68-4682 total 007-007 68-4682 total How do I delete the rows containing "total" One way: 1. Select the range and apply an AutoFilter (Data > Filter > AutoFilter) 2. On the drop-down list, select "Total". 3. Select the range again if needed. 5. Press F5 > Special and click "Visible Cells Only." 6. Remove...

PIVOT tables and publishing thrm on a webpage
Is there a way to publish a pivot table in a html page but still kee exactly the same funcitonality that you have on a Excel spread sheet either by converting it to a java applet or something else . Any hel would be much appreciate -- Message posted from http://www.ExcelForum.com ...

Pivot Table question #4
Hi I have spreadsheets that take data via a pivot table from an OLAP cube that is held on a terminal server. The spreadsheets hold figures entered by me and figures obtain from the pivot table. I want to enable the automatic update on the pivot table but the pivot table is sorted by a project number obtained from the OLAP cube. My problem is if a new project is added to the OLAP cube then it is automatically selected from the drop down list. I want to be able to turn this off so that it only selects the project I have previously chosed. For example if I have selected the filter nu...

Excel Crashes After Autosum or Sum
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Mac OS X 10.6.2 <br> Excel Version 12.2.3 <br><br>My Excel crashes every time I try to use the sum feature. It doesn't matter if I manually type in the formula and hit enter to sum, or if I hit the Autosum button to add up cells, it crashes every time. I only started to do this recently. I haven't made any changes or added any programs to my computer lately that I know of. Any help that you can offer would greatly be appreciated. Excel is basically useless to me at the moment without t...

table and form not calculated in synch
Help says to open a piviot table but I am having trouble making my columns add up to total for each indivudual ID. The totals are being calculated only in th e form view which does not up date the original table and does not transfer to a report. How can I get a feild to calculate specific feilds so that I can run these reports? ...

Excel 2007
When you set reverse categories for the horizontal axis, I noticed that the legend (which I normally placed at the bottom of the chart), did not correspondingly reverse. Is this WAD (working as designed) ? If not, how can i overcome this as the reader would have to mentally "remap" the legend to the bar chart. Thanks very much. Hi, Reversing the Axis categories will not effect the legend order. To do that you need to change the Series Order. In 2007 this is none via the Select Data Source dialog. Chart Tools > Design > Data > Select Data. Use the Up/Down arrows in the...

how do i split a column
i have a worksheet with three columns but want to split column C in to two from row 37 down while keeping only three columns from rows 1-36. How can i do this? You can't. You could merge C1 & D1, C2 & d2 etc. but it seems a bit of overkill. What is the reason for this, there may be another solution? -- HTH RP "craig.d" <craig.d@discussions.microsoft.com> wrote in message news:973F49DB-EF87-4F64-AA13-7AEB3563757A@microsoft.com... > i have a worksheet with three columns but want to split column C in to two > from row 37 down while keeping only three col...

How to you selectively sum on a series of numbers?
I have a column filled with numbers, but I only want to sum up a few of them. Let's say I have numbers filling A1:A10. Let's say I only want to sum up the numbers located is A3 and A4. I'd like to ability to enter something in the b column (e.g. "y" in cells b3 and b4) and have a formula that adds up the values in the "A" cells that have a corresponding "y" in the "B" column. This formula would allow me to selectively choose which cells needs to be summed. What is the best/cleanest way to write that formula? Thank you. =SUMPRODUC...

print folder in table style
Hello all, My CU wants to print a list of files in a folder. He use Outlook 2002. Click Other Shortcuts -> My computer -> choose a folder -> Click the print icon on toolbar -> choose Table Style -> print preview The header and footer can be printed properly. However the content is blank. I mean: there is no file in the list. I can print properly at my side. However at the CU side, there are 2000 users encounter the same problem. And there are four kinds of printer. All have this problem. Any suggestion? Thanks! ...

characters in 1 col, 1 per row
Hi all, I want to transform the text of a worddocument to Excel in a special way. In my case text is just a number of words in a number of lines (nothing special about headers, footers etc.), so something comparable with this post. I want in Excel the first charachter of the text in A1, the second in A2 and so on. This email document would result in: H i a l l , I w a n t etc. I can't figure out how to do this, your help wil be appreciated. Jack Sons The Netherlands ...

GP Extender Tables Question (GP9)
I have created an Extender window with 3 Fields (let’s just say price 1, price 2, price 3). I can see where it got created in table EXT40100, field User_Defined_Prompts_1 etc and I can also see where the data is stored in EXT00103. What I don’t see is where the prompts from EXT40100 are in its own rows so that you can link them to EXT00103 for reporting purposes. I know I can hard code the flags to their proper values in Crystal or set up my own custom table to do it but I would have thought that Extender will have a table like that. Am I just not seeing it? -- Pieter The prompts ...

Creating a Report using Tables (that have no relationships)
Im looking to create an invoice report that uses data from three different tables that are created dynamically by a query. The data for these will change but I would like to simply pull the data from these tables to create a report. The tables have the following information: 1. Company Info (name, address) 2. Customer Info (address, name, etc) 3. Customer Order (products, price, other info) 4. Invoice Info (ID, GST #, etc) I realize I could have created a table with relationships but the data above is dynamic as there are 4 different companies and based upon user inp...

Automatic Insert Rows VBA
A Very Good Evening All, I have a macro that runs: advanced filter/copy to another place/unique, between two worksheets (Column A in each worksheet being the identfier; worksheet1 is source data,with duplicates of identifiers; worksheet2 is formula driven from the sum of the unique identifiers in Worksheet1) However...I would like the macro to automatically extend the rows in worksheet2, to accommodate the ever increasing rows of worksheet1 Here is my specific code worksheet1 = "Invoice Record" worksheet2 = "General Report" Sub CommandButton1_Click() ' A...

Do Pivot Tables have an automatic data range expansion?
As I add new rows of data to my data base, my pivot table will not expand to include them after refreshing. Is there any way of setting the pivot table to include the entire database regardless of its continuously growing size? Right now, as I add a new row of data I must delete my old Pivot table and create a new one from scratch. Is this normal? You can use a dynamic range that grows/contracts with your data. Debra Dalgleish explains it all at: http://www.contextures.com/xlNames01.html#Dynamic David.c.h wrote: > > As I add new rows of data to my data base, my pivot table wil...

Chart
Hello, I created a line column chart on 2 axis. I have four data sets. The first 3 should go on the primary Y axis as columns and the 4th should go on the secondary Y axis as a line. However, Excel wants to automatically put both the 3rd and 4th data sets on the secondary axis as lines. I cannot find any command to change the 3rd data set to go onto the primary axis as a column. Any suggestions would be most appreciated! -Patty On Tue, 11 Nov 2003 13:27:33 -0800, Patty = <anonymous@discussions.microsoft.com> wrote: > Hello, > > I created a line column chart on 2 ...

Some macro help
Lets say I have selected the arbitray range of j31:ax44. I want to run some macro code that will hide every column in that selection where the entire selected column range has nothing in it (blank). Data validation may be assigned to the cells, but if nothing has been selected from the drop down list, then those cells are technically blank. There may be data entered elsewhere in the column, but it is outside of the selected range and thus shouldn't be considered for being hidden or not. Can anyone help me with some code to perform this action? TIA, AlanN How about: Option Explicit Sub...

Excel2000: Can't access an Excel table though ODBC anymore!
Hi I have an Excel file on shared network resource. I have several dynamic named ranges defined there, and 2 fixed named ranges Name1=Sheet1!$B$2:$F$2999 Name2=Sheet2!$A$1:$E$400 In another workbook, I have to query data from table Name2. When I created the query, all worked fine, but now I discovered, that the query isn't working when the source workbook is closed. With closed source workbook, I can see only one named range - Name1 - as data source available. I use ODBC queries quite often, and there never was such problem before, so at moment I am at my wit's end! I'm waiting...

Column names are 1,2,3,....
I just noticed today that the column names in my excel spreadsheets are no longer alphabetical, but are numerical. The column marked "1" is defined by excel as column "1C", and row "1" is defined as row "1R". How can I get the column names to change back to the alphabet? Thanks Hi! G! Use: Tools > Options > General Remove check from "R1C1 reference style" -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good...

conditional formatting: separating rows with differing values
Hi, Need help with conditional fomatting. What I need: I got a worksheet with 950 rows and 5 columns. The first row contain the headers. Columns A, B, D and E contains unique values. But the column C contains text values which repeats sequentially for 6-7 rows and changes thereafter. Now I want to put up a line (using border) through the whole row dividing this transition row. My Idea: Compare C2 with C3, if equal do nothing. Then compare C3 with C4 and so on till 2 consequtive values differ. Now divide the 2 rows using the border formatting option in conditional formatting dialog box. So...

Splitting multiple cell contents from 1st column into 4 columns
n my first column I have 4 cells (1-4) (5-8) etc. with general content. The content from each of these 4 cells needs to be placed in a separate column to make a list that I can sort etc. Example: COLUMN A 1 01-04-425-001 2 Heatherridge Road #301 3 Harry Smith 4 H25 Condo Fairfax Place 5 01-05-356-041 6 McGrath Street # 56 7 Mary Jones 8 B45 Condo Lemon Circle . . . . . . and so on and so on every 4 cells. I have hundreds of 4 cell descriptions. I just can't do this one by one. Can anyone help, PLEASE, PLEASE. I am really a novice at Excel though I use it for e...

Activate email column to hyperlinks
I have a colume of email addresses in an excel 2002 document. I need to make them active hyperlinks - How ? Microsoft instuctinos are useless here. can't they be converted in one batch to active hyperlinks so when the email address is clicked on, the email program opens in a fresh email ? Many thanks for any help http://www.mvps.org/dmcritchie/excel/buildtoc.htm#MakeHyperlinks -- Regards, Peo Sjoblom "DixieWins" <DixieWins@discussions.microsoft.com> wrote in message news:7F59C7F2-63DE-410B-B6FF-0EA29ECC8A35@microsoft.com... > I have a colume of email addr...