Sum and count of unique values

  • Thread starter Thread starter Piotr
  • Start date Start date
P

Piotr

I have a spreadsheet with data, when in A colums is a index (as text),
in B value, like below:

aaa 50.0
ccc 9.9
zzz 5.0
aaa 60.0
ccc 90

In another worksheet I would like to get a table with sum of values of
each unique index, like that:

aaa 110.0
zzz 5.0
ccc 99.9

and in another count of each unique indexes:

aaa 2
ccc 2
zzz 1

How to do it (if it is possible), with Excel functions? And the best
without VBA scripting?

best regards
 
Maybe you could add some headers, sort by the index column, then use
Data|Subtotals (xl2003 menus).

If you really want two sheets, you'll need to duplicate the data across both
sheets (I think a very bad idea).

But you'll be able to use the outlining symbols to see the count/sum for each index.

You can apply data|subtotals twice in a single sheet and accomplish close to
what you want. But both subtotals will be on that single sheet.

But even better...

You could add headers and use pivottables.

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx
 
I have a spreadsheet with data, when in A columns
is a index (as text), in B value, like below:
aaa 50.0
ccc 9.9
zzz 5.0
aaa 60.0
ccc 90
Click to expand...

I put the raw data in Sheet1!A1:B5.

Sheet1 column C is a helper column to determine the unique index
values.

In C1 I put the number 1.

In C2 I put
=IF(A2="","",
IF(COUNTIF(A$1:A1,A2)=0, MAX(C$1:C1)+1, ""))
and copied down to row 10 to allow the list to expand.

In Sheet2!A1 and Sheet3!A1, I put
=IF(ROW()>MAX(Sheet1!$C$1:$C$10),"",
INDEX(Sheet1!$A$1:$A$10,
MATCH(ROW(), Sheet1!$C$1:$C$10,1)))
and copied down to row 10. These are the unique index values.
In another worksheet I would like to get a table with sum
of values of each unique index ...
Click to expand...

In Sheet2!B1, I put
=IF(A1="","",
SUMIF(Sheet1!A$1:A$10,A1,Sheet1!B$1:B$10))
and copied down to row 10.
and in another count of each unique indexes:
Click to expand...

In Sheet3!B1, I put
=IF(A1="","",
COUNTIF(Sheet1!A$1:A$10,A1))
and copied down to row 10.

Modify as needed.
 
W dniu 2011-02-05 23:55, Dave Peterson pisze:

Thanks, pivottable do the job (subtotals doesn't work, still shows
duplicates).
I meantime I found another solution: Advanced Filter from data menu,
with option copy and unique values.
But, both pivottable and advanced filter needs to be manually refreshed
after source data will change. It is possible to do it in automatic way?

regards
 
Personally, I think somethings are better learned so that they can be used in
other worksheets/workbooks/projects.

But if you wanted, you could record a macro when you reset the filter and
reapply it the way you want. Then you can just re-run that macro whenever you
wanted.
 
Back
Top