SUMIF related question

[Steve]

Hi,

In Column A Ihave a list of about 1000 client names, some
are repetitive.

Column B I have number of units sold to clients.

For example I want this data....

Column A Column B
Fred 1
Nancy 5
Albert 25
Fred 0
Albert 5
Fred 7

to look like this on a new worksheet:

Column A Column B
Albert 30
Fred 8
Nancy 5

How do I get Excel to find all rows with Fred, and add
them together? A SUMIF formula would work, but since my
actual Column A contains about 1000 names this would be
too cumbersome to implement. Is there a way for Excel to
automatically sum all the Freds, all the Alberts, and all
the Nancys?

Thanks very much for your help.

 

[JE McGimpsey]

Try a pivot table.

See http://peltiertech.com/Excel/Pivots/pivotstart.htm

 

[Steve]

Thanks... I should have mentioned... is there anyway I
can do it without a pivot table? thanks very much...

 

[Dave Peterson]

I think that this is more work than the pivottable, but...

insert a new worksheet.
select A1
Data|Filter|advanced filter

click the copy to another location button

For the list range, use the mouse to select your original range (just one header
and just one column).

Copy to box becomes A1 of the new sheet.

and check the unique records only

Now you have a list with no duplicates.

You can use a bunch of =sumif()'s.

=SUMIF(Sheet1!A1:A100,A2,Sheet1!B1:B100)
(adjust the range accordingly)

========
Alternatively, you could sort your data and do Data|subtotals.

and use the outlining symbols hide the detail rows.
select those visible cells
edit|goto|special|Visible cells only
copy
paste to a new location.

====
But I think a pivottable will be quicker than either of these!

 

[Harlan Grove]

In Column A Ihave a list of about 1000 client names, some
are repetitive.

Column B I have number of units sold to clients.

For example I want this data....

Column A Column B
Fred 1
Nancy 5
Albert 25
Fred 0
Albert 5
Fred 7

Name this range Tbl.

to look like this on a new worksheet:

Column A Column B
Albert 30
Fred 8
Nancy 5

So Albert in cell A1? If so, it appears you want the names alphabetized. Try

A1 [array formula]:
=INDEX(Tbl,MATCH(0,COUNTIF(INDEX(Tbl,0,1),"<"&INDEX(Tbl,0,1)),0),1)

B1:
=SUMIF(INDEX(Tbl,0,1),A1,INDEX(Tbl,0,2))

A2 [array formula]:
=INDEX(Tbl,MATCH(SUMPRODUCT(COUNTIF(A$1:A1,INDEX(Tbl,0,1))),
COUNTIF(INDEX(Tbl,0,1),"<"&INDEX(Tbl,0,1)),0),1)

B2:
=SUMIF(INDEX(Tbl,0,1),A2,INDEX(Tbl,0,2))

Select A2:B2 and fill down as far as needed. The column A formulas return
#N/A once you've exhausted the distinct names in the first column of Tbl.

 

[Frank Kabel]

Harlan Grove wrote:
[....]

A2 [array formula]:
=INDEX(Tbl,MATCH(SUMPRODUCT(COUNTIF(A$1:A1,INDEX(Tbl,0,1))),
COUNTIF(INDEX(Tbl,0,1),"<"&INDEX(Tbl,0,1)),0),1)

Hi Harlan
just wondering why you use SUMPRODUCT in this case instead of SUM. At
least for me it does return the same results for this specific case
(array entered formula). So
=INDEX(Tbl,MATCH(SUM(COUNTIF(A$1:A1,INDEX(Tbl,0,1))),COUNTIF(INDEX(Tbl,
0,1),"<"&INDEX(Tbl,0,1)),0),1)

would also work.

Regards
Frank

 

[Harlan Grove]

just wondering why you use SUMPRODUCT in this case instead of SUM. At
least for me it does return the same results for this specific case

....

No good reason. I was lazy, and I usually use SUMPRODUCT around COUNTIF, but
SUM is sufficient for this.

 

[Guest]

Frank, Harlan, Dave, JE -- thanks for getting back to me... you are right, a pivot table is much easier to create and flexible to use... I"ve never had to work with 17000 lines of data (I've only looked at aggrated financial statements before, not actual details), so I was kind of blindly pushing ahead... Thanks again for your help.