Use subtotal function on two columns

  • Thread starter Thread starter Thomas Guignard
  • Start date Start date
T

Thomas Guignard

Hi there

Here's our problem. We have a table like this:

58 5'630.00
58 997.00
58 489.00
58 385.44
57 2'244.00
57 2'228.00
57 1'451.24
57 576.00
57 0.00
56 2'752.00
56 1'644.00
56 544.58
56 472.31
55 927.00
54 3'068.00
54 1'868.00

What we want to have, is a table with the number of occurences of a
particular number in the 1st colum and the sum of the relevant numbers
in the 2nd colum. In our case:

Number Occurences Sum
58 4 7'501.44
57 5 6'499.24
56 4 ...

We found out how to make one, or the other, using the "Subtotal" menu
item, but were not able to combine the two steps.
It is very important to have the # of occurences and the sum on the same
line, since we are using this data to build a graph.

Thanks for your help!
 
Thomas

I would build another table with the first number in the first column (Say
A)

58
57
56
55
54

and then alongside use a COUNTIF for the count of these and alongside that a
SUMIF to sum the totals against each one

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non tech): www.nickhodge.co.uk/blog
 
List your unique numbers (58, 57, 56 etc) let's say in column F
starting in F2 with appropriate headings in F1:H1. Then in G2 enter
this formula:

=COUNTIF(A:A,F2)

and this one in H2:

=SUMIF(A:A,F2,B:B)

Copy these two formulae down to cover the numbers in column F.

Hope this helps.

Pete
 
Hi and thanks for your replies,

This is a nice idea, the problem is that I was merely proposing an
example, my list is way longer than that. Is there a way to extract the
unique numbers?

In SQL, my request would have been completed very easily, using the
GROUP BY function. How come there is no GROUP possibility in Excel?

Thanks again for your help.

Pete_UK a écrit :
 
To obtain unique numbers, all you need to do is highlight the data
plus the heading (you must have a heading), then Data | Filter |
Advanced filter. In the pop-up you should click on Unique Records only
and Copy to Another location, and specify where you want the list to
start - F1 in my example, as the header is produced as part of it.
Click OK and you have your unique list.

Then you can follow the rest of it.

Hope this helps.

Pete
 
Hi Thomas

Insert a new row 1 and give titles to both columns
Mark columns A and B.
Data>Pivot Table>Next>Layout
Drag your field name for Column A to the Row Area
Drag the field name for column B to the Data Area>double click label>choose
Sum
Drag the field name for column B again to the Data Area>double click
label>choose Count
OK>Finish
Click to expand...

On the PT report that is created, drag the Data label to Total and your Sum
and Count will appear side by side.

Click any cell on the PT>F11 and you will get a Chart
 
Back
Top