Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif with multiple sets of duplicates
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... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif with multiple sets of duplicates
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/assistan...lconPT101.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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Lou Nunez" wrote in message ... 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... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif with multiple sets of duplicates
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" wrote in message ... 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 __________ Information from ESET Smart Security, version of virus signature database 4536 (20091023) __________ The message was checked by ESET Smart Security. http://www.eset.com |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif with multiple sets of duplicates
On Oct 23, 11:38*am, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote: 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" wrote in message ... 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 __________ Information from ESET Smart Security, version of virus signature database 4536 (20091023) __________ The message was checked by ESET Smart Security. http://www.eset.com 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif with multiple sets of duplicates
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 "Lou Nunez" wrote: 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... . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: Sumif function with two sets of criteria? Can it be done? | Excel Discussion (Misc queries) | |||
is it possible to combine data sets, eliminating duplicates? | Excel Worksheet Functions | |||
How to generate sets of random numbers without having duplicates | Excel Worksheet Functions | |||
SUMIF with two sets of criteria | Excel Discussion (Misc queries) | |||
for SUMIF function, how do I use 2 sets of range & criteria | Excel Worksheet Functions |