how to create subtotals based on column sort (pivot table)

Question

Level 1

4 points

how to create subtotals based on column sort (pivot table)

I have a spreadsheet that contains a list of invoice numbers and then detailed/line items, cost and retail for those invoices. Thus invoice number is repeated for each line item. I want to create a SUBTOTAL of each line item (for both cost and retail) for EACH invoice number. (display in a separate column??). I THINK this is similar to a pivot table in excell (but I've never used those either). I believe this may be a SUMIF function, but not sure how to use those (how to setup function/equation).

sample data below. The results of the calculation that I want to create would be:

inv 100 cost = 9 retail = 24;

inv 101 cost = 10 retail = 25

inv 102 cost = 14 retail = 35

Any help would be appreciated.

Thanks!!

invoice	cost	retail
100	4	10
100	3	8
100	2	6
101	4	10
101	6	15
102	4	10
102	2	5
102	8	20

macbook pro, Mac OS X (10.5.8)

Posted on Feb 21, 2014 2:08 PM

Reply

Answer 1

Feb 21, 2014 3:12 PM in response to DK Quilter

Ther "Numbers Way" is to add a second table which is used to summarize that information. Like this:

Name the original table you posted "Data" and create new table and title it "Summary".

In the table on the right (the "Summary" table) enter the invoice numbers in column A

In cell B2 enter (or copy and paste from here) the formula:

=IF(ISBLANK($A2),"",SUMIF(Data::$A, $A2, Data::$B))

B2=IF(ISBLANK($A2),"",SUMIF(Data::$A, $A2, Data::$B))

C2=IF(ISBLANK($A2),"",SUMIF(Data::$A, $A2, Data::$C))

select B2 and C2, now fill down as needed by hovering the cursor over the bottom edge and drag the little yellow fill dot down

Reply

Answer 2

DK Quilter Author

Level 1

4 points

Feb 21, 2014 7:18 PM in response to Wayne Contello

Thank you Wayne!!

My results looked more like this (below), but at least I have the calculations that I need!!

Just out of curiousity...IS there a way to get each unique value to appear only one time?

That way, I can get a total at the end of each column and it would be accurate.

Again...this really helped me! Thanks SO very much!

inv #	extended retail	extended cost
100	9	24
100	9	24
100	9	24
101	10	25
101	10	25
102	14	35
102	14	35
102	14	35

Reply