Sumif with multiple sets of duplicates

  • Thread starter Thread starter Lou Nunez
  • Start date Start date
L

Lou Nunez

Hi,

I have a very very long list with intentional duplicates. Each school
name is repeated and paired with a different language and a count of
how many parents speak that language at home. So for example

Spanish - School1 - 28
Chinese - School1 - 10
Arabic - School1 - 5
Spanish - School2 - 28
Chinese - School2 - 10
Arabic - School2 - 5
Korean - School2 - 2

etc.

I want to sum up the totals for each school but there are 1,000+
schools which repeat without a set pattern. What formula could I use
to add totals for a set? I hope this is clear...
 
There are ways to filter list and then extract a list with out duplicates.
Then you could use SUMPRODUCT for each school/language pair
But really this is a job for pivot tables.
It will take you a little time (say 1 hour) to catch on the them but power
of pivot tables will impress you.


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


Happy to show you the results if you send me a file (get my email addy from
my website)
Tell me if you use Excel 2007 or 2003?
best wishes
 
Hi Lou

Assuming your data is in columns A b and C of sheet1
On sheet2 in column A create the List of School1 through School1000
In B1 of Sheet2
=SUMPRODUCT(--(Sheet1!$B$1:$B$10000=A1),Sheet1!$C$1:$C$10000)

assuming 10,000 rows is enough to cover your data on sheet 1 - else extend
ranges.

If you wanted the same thing but to count the totals by each language, on
Sheet3 enter languages in column A and in column B enter
=SUMPRODUCT(--(Sheet1!$A$1:$A$10000=A1),Sheet1!$C$1:$C$10000)

--
Regards
Roger Govier

Lou Nunez said:
Hi,

I have a very very long list with intentional duplicates. Each school
name is repeated and paired with a different language and a count of
how many parents speak that language at home. So for example

Spanish - School1 - 28
Chinese - School1 - 10
Arabic - School1 - 5
Spanish - School2 - 28
Chinese - School2 - 10
Arabic - School2 - 5
Korean - School2 - 2

etc.

I want to sum up the totals for each school but there are 1,000+
schools which repeat without a set pattern. What formula could I use
to add totals for a set? I hope this is clear...

__________ Information from ESET Smart Security, version of virus
signature database 4536 (20091023) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4536 (20091023) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Hi Lou

Assuming your data is in columns A b and C of sheet1
On sheet2 in column A create the List of School1 through School1000
In B1 of Sheet2
=SUMPRODUCT(--(Sheet1!$B$1:$B$10000=A1),Sheet1!$C$1:$C$10000)

assuming 10,000 rows is enough to cover your data on sheet 1 - else extend
ranges.

If you wanted the same thing but to count the totals by each language, on
Sheet3 enter languages in column A and in column B enter
=SUMPRODUCT(--(Sheet1!$A$1:$A$10000=A1),Sheet1!$C$1:$C$10000)

--
Regards
Roger Govier











__________ Information from ESET Smart Security, version of virus signature database 4536 (20091023) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

Thank you both so much. I think I get it (had a breakthrough moment
when reading your responses). Hopefully I can get it to work.
 
A pivot table should be perfect for this. If your data is in separate columns
(languages, schools, and # of speakers each in its own column), you can
create a pivot table with school as a row field, language as either a row or
a column field, and the number of speakers as the data field.

If your data is in one column with dashes separating the fields (as your
post depicts), use Text to Columns to split it into separate columns, then
create the pivot table as described above.

If you are new to pivot tables, there is lots of instruction available on
Debra Dalgleish's excellent Contextures site:

http://www.contextures.com/tiptech.html

Hope this helps,

Hutch
 
Back
Top