Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate totals for a range of names
Still hunting for a solution to a problem posted earlier. I have a data
table that contains a list of donations, with the name of the donor in column A and the amount of each donation in column B. Some donors gave more than once, and I need to be able to calculate their aggregate donations. In the end, I'm only interested in displaying totals for donors who gave more than $1,600 -- whether through a single donations, or multiple donations. I want to display the totals in column C. What formula will allow me to calculate totals for all donors, including names that are similar but not exact matches (e.g., grouping PERKINS JOHN W together with PERKINS JOHN), and then show totals *only* for donors whose total donations exceed $1,600? Here's some sample data -- in my actual data table, there are thousands of rows, and donors have given anywhere from one to ten or more times. As you can see, the names aren't always entered the same way. My early research indicates that I should maybe use the LEFT function to compare the beginning characters of names, but please tell me if there's another way. A B C Contributor Name Amount Over 1,600? ABBOTT DOROTHY C 1500 ABBOTT DOROTHY C 15 ANDREWS KATE 100 ANDREWS KATE J 50 BROWN MICHAEL 25 DALTON KUMI 1400 FRENCH KATE K 1550 FRENCH KATHERINE K 100 GORDON NANCY 10 LINCOLN EDDIE 25 LINCOLN ELLEN 25 LINCOLN ELLEN M 1000 LINCOLN ELLEN M 800 LINCOLN ELLEN M 50 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate totals for a range of names
If you assume that there are *no duplicate* last names, use TTC (Text To
Columns) to create a "helper" column, and have your formula reference that helper column to perform your calculations. With data in say A2 to Bn, select A2 to An, and then, from the Menu Bar, <Data <Text To Columns Click on "Delimited". then <Next, Then click on "Space", then <Next, In the "Data Preview" window, the left most column is selected by default. In the "Destination" box, enter D2 Then click in the next column (second from left) to select it, And click on "Do Not Import". You'll see the column header in the Preview window change to "Skip". Do the same to any other columns remaining, leaving only the first, left column header displaying "General". Then click <Finish You should now have all your original data, with your "helper" Column D displaying only the last names from Column A. Now, in C2, enter this formula: =IF(SUMPRODUCT(($D$2:$D$50=D2)*$B$2:$B$50)1600,SU MPRODUCT(($D$2:$D$50=D2)*$B$2:$B$50),"") And copy down as needed. I've sized this formula for only 50 rows. Adjust the ranges to suit. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "t_perkins" wrote in message ... Still hunting for a solution to a problem posted earlier. I have a data table that contains a list of donations, with the name of the donor in column A and the amount of each donation in column B. Some donors gave more than once, and I need to be able to calculate their aggregate donations. In the end, I'm only interested in displaying totals for donors who gave more than $1,600 -- whether through a single donations, or multiple donations. I want to display the totals in column C. What formula will allow me to calculate totals for all donors, including names that are similar but not exact matches (e.g., grouping PERKINS JOHN W together with PERKINS JOHN), and then show totals *only* for donors whose total donations exceed $1,600? Here's some sample data -- in my actual data table, there are thousands of rows, and donors have given anywhere from one to ten or more times. As you can see, the names aren't always entered the same way. My early research indicates that I should maybe use the LEFT function to compare the beginning characters of names, but please tell me if there's another way. A B C Contributor Name Amount Over 1,600? ABBOTT DOROTHY C 1500 ABBOTT DOROTHY C 15 ANDREWS KATE 100 ANDREWS KATE J 50 BROWN MICHAEL 25 DALTON KUMI 1400 FRENCH KATE K 1550 FRENCH KATHERINE K 100 GORDON NANCY 10 LINCOLN EDDIE 25 LINCOLN ELLEN 25 LINCOLN ELLEN M 1000 LINCOLN ELLEN M 800 LINCOLN ELLEN M 50 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate totals for a range of names
Hi, Don. To begin with, I don't think it will work to test only for a few
specific characters, like "abb." I need to be able to group together all the ABBOTT DOROTHY C's, all the LINCOLN ELLEN's, and to group together the FRENCH KATE's with the FRENCH KATHERINE's. These are just examples -- I'm hoping to construct a formula that will calculate the total donations for each name in the list and each *similar* name, and then display only the totals that exceed $1,600. Here are the correct answers for the sample data I pasted in this latest thread. The formula would display the following results in column C: A B C Contributor Name Amount Over 1,600? FRENCH KATE K 1550 1650 FRENCH KATHERINE K 100 LINCOLN ELLEN 25 1875 LINCOLN ELLEN M 1000 LINCOLN ELLEN M 800 LINCOLN ELLEN M 50 Column C would be blank in the rest of the rows. The formula would calculate the total donations for *all* names in column A, allowing for slight variations (e.g., ANDREWS KATE and ANDREWS KATE J) but it would only display totals in column C when the total exceeded $1,600. Tell me if I'm still not being clear. I really appreciate your help. "Don Guillett" wrote: What about my answer to your previous post did NOT work?? You need to show examples of the correct answer for a couple of your donors. Either I don't understand or you are NOT explaining yourself well. =IF(SUMPRODUCT(--(LEFT($a$3:$a$3333,3)="abb"),$b$3:$b$33)<1600,"",S UMPRODUCT(--(LEFT($a$3:$a$3333,3)="abb"),$b$3:$b$3333)) In this case LIN would show 1900 -- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... Still hunting for a solution to a problem posted earlier. I have a data table that contains a list of donations, with the name of the donor in column A and the amount of each donation in column B. Some donors gave more than once, and I need to be able to calculate their aggregate donations. In the end, I'm only interested in displaying totals for donors who gave more than $1,600 -- whether through a single donations, or multiple donations. I want to display the totals in column C. What formula will allow me to calculate totals for all donors, including names that are similar but not exact matches (e.g., grouping PERKINS JOHN W together with PERKINS JOHN), and then show totals *only* for donors whose total donations exceed $1,600? Here's some sample data -- in my actual data table, there are thousands of rows, and donors have given anywhere from one to ten or more times. As you can see, the names aren't always entered the same way. My early research indicates that I should maybe use the LEFT function to compare the beginning characters of names, but please tell me if there's another way. A B C Contributor Name Amount Over 1,600? ABBOTT DOROTHY C 1500 ABBOTT DOROTHY C 15 ANDREWS KATE 100 ANDREWS KATE J 50 BROWN MICHAEL 25 DALTON KUMI 1400 FRENCH KATE K 1550 FRENCH KATHERINE K 100 GORDON NANCY 10 LINCOLN EDDIE 25 LINCOLN ELLEN 25 LINCOLN ELLEN M 1000 LINCOLN ELLEN M 800 LINCOLN ELLEN M 50 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate totals for a range of names
There *will* be duplicate last names, but I'm going to try your suggestion
anyway to see if I can understand how your formula works. I'll report back tomorrow -- it's closing time here. Thanks much! "RagDyer" wrote: If you assume that there are *no duplicate* last names, use TTC (Text To Columns) to create a "helper" column, and have your formula reference that helper column to perform your calculations. With data in say A2 to Bn, select A2 to An, and then, from the Menu Bar, <Data <Text To Columns Click on "Delimited". then <Next, Then click on "Space", then <Next, In the "Data Preview" window, the left most column is selected by default. In the "Destination" box, enter D2 Then click in the next column (second from left) to select it, And click on "Do Not Import". You'll see the column header in the Preview window change to "Skip". Do the same to any other columns remaining, leaving only the first, left column header displaying "General". Then click <Finish You should now have all your original data, with your "helper" Column D displaying only the last names from Column A. Now, in C2, enter this formula: =IF(SUMPRODUCT(($D$2:$D$50=D2)*$B$2:$B$50)1600,SU MPRODUCT(($D$2:$D$50=D2)*$B$2:$B$50),"") And copy down as needed. I've sized this formula for only 50 rows. Adjust the ranges to suit. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "t_perkins" wrote in message ... Still hunting for a solution to a problem posted earlier. I have a data table that contains a list of donations, with the name of the donor in column A and the amount of each donation in column B. Some donors gave more than once, and I need to be able to calculate their aggregate donations. In the end, I'm only interested in displaying totals for donors who gave more than $1,600 -- whether through a single donations, or multiple donations. I want to display the totals in column C. What formula will allow me to calculate totals for all donors, including names that are similar but not exact matches (e.g., grouping PERKINS JOHN W together with PERKINS JOHN), and then show totals *only* for donors whose total donations exceed $1,600? Here's some sample data -- in my actual data table, there are thousands of rows, and donors have given anywhere from one to ten or more times. As you can see, the names aren't always entered the same way. My early research indicates that I should maybe use the LEFT function to compare the beginning characters of names, but please tell me if there's another way. A B C Contributor Name Amount Over 1,600? ABBOTT DOROTHY C 1500 ABBOTT DOROTHY C 15 ANDREWS KATE 100 ANDREWS KATE J 50 BROWN MICHAEL 25 DALTON KUMI 1400 FRENCH KATE K 1550 FRENCH KATHERINE K 100 GORDON NANCY 10 LINCOLN EDDIE 25 LINCOLN ELLEN 25 LINCOLN ELLEN M 1000 LINCOLN ELLEN M 800 LINCOLN ELLEN M 50 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate totals for a range of names
Totals col B if Last Name and 1st three letters of first name match in col
A. Change $33 to $whatever =IF(COUNTIF($H$3:H3,H3)<1,"",IF(SUMPRODUCT((LEFT( $A$3:$A$33,FIND(" ",$A3))=LEFT($A3,FIND(" ",$A3)))*((MID($A$3:$A$33,FIND(" ",$A3),4)=MID($A3,FIND(" ",$A3),4))*$B$3:$B$33))<1600,"",SUMPRODUCT((LEFT($ A$3:$A$33,FIND(" ",$A3))=LEFT($A3,FIND(" ",$A3)))*((MID($A$3:$A$33,FIND(" ",$A3),4)=MID($A3,FIND(" ",$A3),4))*$B$3:$B$33)))) -- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... Hi, Don. To begin with, I don't think it will work to test only for a few specific characters, like "abb." I need to be able to group together all the ABBOTT DOROTHY C's, all the LINCOLN ELLEN's, and to group together the FRENCH KATE's with the FRENCH KATHERINE's. These are just examples -- I'm hoping to construct a formula that will calculate the total donations for each name in the list and each *similar* name, and then display only the totals that exceed $1,600. Here are the correct answers for the sample data I pasted in this latest thread. The formula would display the following results in column C: A B C Contributor Name Amount Over 1,600? FRENCH KATE K 1550 1650 FRENCH KATHERINE K 100 LINCOLN ELLEN 25 1875 LINCOLN ELLEN M 1000 LINCOLN ELLEN M 800 LINCOLN ELLEN M 50 Column C would be blank in the rest of the rows. The formula would calculate the total donations for *all* names in column A, allowing for slight variations (e.g., ANDREWS KATE and ANDREWS KATE J) but it would only display totals in column C when the total exceeded $1,600. Tell me if I'm still not being clear. I really appreciate your help. "Don Guillett" wrote: What about my answer to your previous post did NOT work?? You need to show examples of the correct answer for a couple of your donors. Either I don't understand or you are NOT explaining yourself well. =IF(SUMPRODUCT(--(LEFT($a$3:$a$3333,3)="abb"),$b$3:$b$33)<1600,"",S UMPRODUCT(--(LEFT($a$3:$a$3333,3)="abb"),$b$3:$b$3333)) In this case LIN would show 1900 -- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... Still hunting for a solution to a problem posted earlier. I have a data table that contains a list of donations, with the name of the donor in column A and the amount of each donation in column B. Some donors gave more than once, and I need to be able to calculate their aggregate donations. In the end, I'm only interested in displaying totals for donors who gave more than $1,600 -- whether through a single donations, or multiple donations. I want to display the totals in column C. What formula will allow me to calculate totals for all donors, including names that are similar but not exact matches (e.g., grouping PERKINS JOHN W together with PERKINS JOHN), and then show totals *only* for donors whose total donations exceed $1,600? Here's some sample data -- in my actual data table, there are thousands of rows, and donors have given anywhere from one to ten or more times. As you can see, the names aren't always entered the same way. My early research indicates that I should maybe use the LEFT function to compare the beginning characters of names, but please tell me if there's another way. A B C Contributor Name Amount Over 1,600? ABBOTT DOROTHY C 1500 ABBOTT DOROTHY C 15 ANDREWS KATE 100 ANDREWS KATE J 50 BROWN MICHAEL 25 DALTON KUMI 1400 FRENCH KATE K 1550 FRENCH KATHERINE K 100 GORDON NANCY 10 LINCOLN EDDIE 25 LINCOLN ELLEN 25 LINCOLN ELLEN M 1000 LINCOLN ELLEN M 800 LINCOLN ELLEN M 50 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate totals for a range of names
I forgot that this includes a helper col H of
=LEFT($A$3:$A$33,FIND(" ",$A3)) & MID($A$3:$A$33,FIND(" ",$A3),4) -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Totals col B if Last Name and 1st three letters of first name match in col A. Change $33 to $whatever =IF(COUNTIF($H$3:H3,H3)<1,"",IF(SUMPRODUCT((LEFT( $A$3:$A$33,FIND(" ",$A3))=LEFT($A3,FIND(" ",$A3)))*((MID($A$3:$A$33,FIND(" ",$A3),4)=MID($A3,FIND(" ",$A3),4))*$B$3:$B$33))<1600,"",SUMPRODUCT((LEFT($ A$3:$A$33,FIND(" ",$A3))=LEFT($A3,FIND(" ",$A3)))*((MID($A$3:$A$33,FIND(" ",$A3),4)=MID($A3,FIND(" ",$A3),4))*$B$3:$B$33)))) -- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... Hi, Don. To begin with, I don't think it will work to test only for a few specific characters, like "abb." I need to be able to group together all the ABBOTT DOROTHY C's, all the LINCOLN ELLEN's, and to group together the FRENCH KATE's with the FRENCH KATHERINE's. These are just examples -- I'm hoping to construct a formula that will calculate the total donations for each name in the list and each *similar* name, and then display only the totals that exceed $1,600. Here are the correct answers for the sample data I pasted in this latest thread. The formula would display the following results in column C: A B C Contributor Name Amount Over 1,600? FRENCH KATE K 1550 1650 FRENCH KATHERINE K 100 LINCOLN ELLEN 25 1875 LINCOLN ELLEN M 1000 LINCOLN ELLEN M 800 LINCOLN ELLEN M 50 Column C would be blank in the rest of the rows. The formula would calculate the total donations for *all* names in column A, allowing for slight variations (e.g., ANDREWS KATE and ANDREWS KATE J) but it would only display totals in column C when the total exceeded $1,600. Tell me if I'm still not being clear. I really appreciate your help. "Don Guillett" wrote: What about my answer to your previous post did NOT work?? You need to show examples of the correct answer for a couple of your donors. Either I don't understand or you are NOT explaining yourself well. =IF(SUMPRODUCT(--(LEFT($a$3:$a$3333,3)="abb"),$b$3:$b$33)<1600,"",S UMPRODUCT(--(LEFT($a$3:$a$3333,3)="abb"),$b$3:$b$3333)) In this case LIN would show 1900 -- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... Still hunting for a solution to a problem posted earlier. I have a data table that contains a list of donations, with the name of the donor in column A and the amount of each donation in column B. Some donors gave more than once, and I need to be able to calculate their aggregate donations. In the end, I'm only interested in displaying totals for donors who gave more than $1,600 -- whether through a single donations, or multiple donations. I want to display the totals in column C. What formula will allow me to calculate totals for all donors, including names that are similar but not exact matches (e.g., grouping PERKINS JOHN W together with PERKINS JOHN), and then show totals *only* for donors whose total donations exceed $1,600? Here's some sample data -- in my actual data table, there are thousands of rows, and donors have given anywhere from one to ten or more times. As you can see, the names aren't always entered the same way. My early research indicates that I should maybe use the LEFT function to compare the beginning characters of names, but please tell me if there's another way. A B C Contributor Name Amount Over 1,600? ABBOTT DOROTHY C 1500 ABBOTT DOROTHY C 15 ANDREWS KATE 100 ANDREWS KATE J 50 BROWN MICHAEL 25 DALTON KUMI 1400 FRENCH KATE K 1550 FRENCH KATHERINE K 100 GORDON NANCY 10 LINCOLN EDDIE 25 LINCOLN ELLEN 25 LINCOLN ELLEN M 1000 LINCOLN ELLEN M 800 LINCOLN ELLEN M 50 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate totals for a range of names
Still having a problem -- I'll email you a worksheet with your formulas
pasted in. Thanks, Tony "Don Guillett" wrote: I forgot that this includes a helper col H of =LEFT($A$3:$A$33,FIND(" ",$A3)) & MID($A$3:$A$33,FIND(" ",$A3),4) -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Totals col B if Last Name and 1st three letters of first name match in col A. Change $33 to $whatever =IF(COUNTIF($H$3:H3,H3)<1,"",IF(SUMPRODUCT((LEFT( $A$3:$A$33,FIND(" ",$A3))=LEFT($A3,FIND(" ",$A3)))*((MID($A$3:$A$33,FIND(" ",$A3),4)=MID($A3,FIND(" ",$A3),4))*$B$3:$B$33))<1600,"",SUMPRODUCT((LEFT($ A$3:$A$33,FIND(" ",$A3))=LEFT($A3,FIND(" ",$A3)))*((MID($A$3:$A$33,FIND(" ",$A3),4)=MID($A3,FIND(" ",$A3),4))*$B$3:$B$33)))) -- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... Hi, Don. To begin with, I don't think it will work to test only for a few specific characters, like "abb." I need to be able to group together all the ABBOTT DOROTHY C's, all the LINCOLN ELLEN's, and to group together the FRENCH KATE's with the FRENCH KATHERINE's. These are just examples -- I'm hoping to construct a formula that will calculate the total donations for each name in the list and each *similar* name, and then display only the totals that exceed $1,600. Here are the correct answers for the sample data I pasted in this latest thread. The formula would display the following results in column C: A B C Contributor Name Amount Over 1,600? FRENCH KATE K 1550 1650 FRENCH KATHERINE K 100 LINCOLN ELLEN 25 1875 LINCOLN ELLEN M 1000 LINCOLN ELLEN M 800 LINCOLN ELLEN M 50 Column C would be blank in the rest of the rows. The formula would calculate the total donations for *all* names in column A, allowing for slight variations (e.g., ANDREWS KATE and ANDREWS KATE J) but it would only display totals in column C when the total exceeded $1,600. Tell me if I'm still not being clear. I really appreciate your help. "Don Guillett" wrote: What about my answer to your previous post did NOT work?? You need to show examples of the correct answer for a couple of your donors. Either I don't understand or you are NOT explaining yourself well. =IF(SUMPRODUCT(--(LEFT($a$3:$a$3333,3)="abb"),$b$3:$b$33)<1600,"",S UMPRODUCT(--(LEFT($a$3:$a$3333,3)="abb"),$b$3:$b$3333)) In this case LIN would show 1900 -- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... Still hunting for a solution to a problem posted earlier. I have a data table that contains a list of donations, with the name of the donor in column A and the amount of each donation in column B. Some donors gave more than once, and I need to be able to calculate their aggregate donations. In the end, I'm only interested in displaying totals for donors who gave more than $1,600 -- whether through a single donations, or multiple donations. I want to display the totals in column C. What formula will allow me to calculate totals for all donors, including names that are similar but not exact matches (e.g., grouping PERKINS JOHN W together with PERKINS JOHN), and then show totals *only* for donors whose total donations exceed $1,600? Here's some sample data -- in my actual data table, there are thousands of rows, and donors have given anywhere from one to ten or more times. As you can see, the names aren't always entered the same way. My early research indicates that I should maybe use the LEFT function to compare the beginning characters of names, but please tell me if there's another way. A B C Contributor Name Amount Over 1,600? ABBOTT DOROTHY C 1500 ABBOTT DOROTHY C 15 ANDREWS KATE 100 ANDREWS KATE J 50 BROWN MICHAEL 25 DALTON KUMI 1400 FRENCH KATE K 1550 FRENCH KATHERINE K 100 GORDON NANCY 10 LINCOLN EDDIE 25 LINCOLN ELLEN 25 LINCOLN ELLEN M 1000 LINCOLN ELLEN M 800 LINCOLN ELLEN M 50 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate totals for a range of names
I think I may have figured it out, based on help from you and RagDyer!
=IF(SUMPRODUCT(((LEFT($A$2:$A$14,12))=(LEFT(A2,12) ))*$B$2:$B$14)1600,SUMPRODUCT(((LEFT($A$2:$A$14,1 2))=(LEFT(A2,12)))*$B$2:$B$14),"") No helper column needed!!! I'll keep testing it to see if I'm right. "t_perkins" wrote: Still having a problem -- I'll email you a worksheet with your formulas pasted in. Thanks, Tony "Don Guillett" wrote: I forgot that this includes a helper col H of =LEFT($A$3:$A$33,FIND(" ",$A3)) & MID($A$3:$A$33,FIND(" ",$A3),4) -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Totals col B if Last Name and 1st three letters of first name match in col A. Change $33 to $whatever =IF(COUNTIF($H$3:H3,H3)<1,"",IF(SUMPRODUCT((LEFT( $A$3:$A$33,FIND(" ",$A3))=LEFT($A3,FIND(" ",$A3)))*((MID($A$3:$A$33,FIND(" ",$A3),4)=MID($A3,FIND(" ",$A3),4))*$B$3:$B$33))<1600,"",SUMPRODUCT((LEFT($ A$3:$A$33,FIND(" ",$A3))=LEFT($A3,FIND(" ",$A3)))*((MID($A$3:$A$33,FIND(" ",$A3),4)=MID($A3,FIND(" ",$A3),4))*$B$3:$B$33)))) -- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... Hi, Don. To begin with, I don't think it will work to test only for a few specific characters, like "abb." I need to be able to group together all the ABBOTT DOROTHY C's, all the LINCOLN ELLEN's, and to group together the FRENCH KATE's with the FRENCH KATHERINE's. These are just examples -- I'm hoping to construct a formula that will calculate the total donations for each name in the list and each *similar* name, and then display only the totals that exceed $1,600. Here are the correct answers for the sample data I pasted in this latest thread. The formula would display the following results in column C: A B C Contributor Name Amount Over 1,600? FRENCH KATE K 1550 1650 FRENCH KATHERINE K 100 LINCOLN ELLEN 25 1875 LINCOLN ELLEN M 1000 LINCOLN ELLEN M 800 LINCOLN ELLEN M 50 Column C would be blank in the rest of the rows. The formula would calculate the total donations for *all* names in column A, allowing for slight variations (e.g., ANDREWS KATE and ANDREWS KATE J) but it would only display totals in column C when the total exceeded $1,600. Tell me if I'm still not being clear. I really appreciate your help. "Don Guillett" wrote: What about my answer to your previous post did NOT work?? You need to show examples of the correct answer for a couple of your donors. Either I don't understand or you are NOT explaining yourself well. =IF(SUMPRODUCT(--(LEFT($a$3:$a$3333,3)="abb"),$b$3:$b$33)<1600,"",S UMPRODUCT(--(LEFT($a$3:$a$3333,3)="abb"),$b$3:$b$3333)) In this case LIN would show 1900 -- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... Still hunting for a solution to a problem posted earlier. I have a data table that contains a list of donations, with the name of the donor in column A and the amount of each donation in column B. Some donors gave more than once, and I need to be able to calculate their aggregate donations. In the end, I'm only interested in displaying totals for donors who gave more than $1,600 -- whether through a single donations, or multiple donations. I want to display the totals in column C. What formula will allow me to calculate totals for all donors, including names that are similar but not exact matches (e.g., grouping PERKINS JOHN W together with PERKINS JOHN), and then show totals *only* for donors whose total donations exceed $1,600? Here's some sample data -- in my actual data table, there are thousands of rows, and donors have given anywhere from one to ten or more times. As you can see, the names aren't always entered the same way. My early research indicates that I should maybe use the LEFT function to compare the beginning characters of names, but please tell me if there's another way. A B C Contributor Name Amount Over 1,600? ABBOTT DOROTHY C 1500 ABBOTT DOROTHY C 15 ANDREWS KATE 100 ANDREWS KATE J 50 BROWN MICHAEL 25 DALTON KUMI 1400 FRENCH KATE K 1550 FRENCH KATHERINE K 100 GORDON NANCY 10 LINCOLN EDDIE 25 LINCOLN ELLEN 25 LINCOLN ELLEN M 1000 LINCOLN ELLEN M 800 LINCOLN ELLEN M 50 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate totals for a range of names
I sent you a workbook with the proper answers
-- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... I think I may have figured it out, based on help from you and RagDyer! =IF(SUMPRODUCT(((LEFT($A$2:$A$14,12))=(LEFT(A2,12) ))*$B$2:$B$14)1600,SUMPRODUCT(((LEFT($A$2:$A$14,1 2))=(LEFT(A2,12)))*$B$2:$B$14),"") No helper column needed!!! I'll keep testing it to see if I'm right. "t_perkins" wrote: Still having a problem -- I'll email you a worksheet with your formulas pasted in. Thanks, Tony "Don Guillett" wrote: I forgot that this includes a helper col H of =LEFT($A$3:$A$33,FIND(" ",$A3)) & MID($A$3:$A$33,FIND(" ",$A3),4) -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Totals col B if Last Name and 1st three letters of first name match in col A. Change $33 to $whatever =IF(COUNTIF($H$3:H3,H3)<1,"",IF(SUMPRODUCT((LEFT( $A$3:$A$33,FIND(" ",$A3))=LEFT($A3,FIND(" ",$A3)))*((MID($A$3:$A$33,FIND(" ",$A3),4)=MID($A3,FIND(" ",$A3),4))*$B$3:$B$33))<1600,"",SUMPRODUCT((LEFT($ A$3:$A$33,FIND(" ",$A3))=LEFT($A3,FIND(" ",$A3)))*((MID($A$3:$A$33,FIND(" ",$A3),4)=MID($A3,FIND(" ",$A3),4))*$B$3:$B$33)))) -- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... Hi, Don. To begin with, I don't think it will work to test only for a few specific characters, like "abb." I need to be able to group together all the ABBOTT DOROTHY C's, all the LINCOLN ELLEN's, and to group together the FRENCH KATE's with the FRENCH KATHERINE's. These are just examples -- I'm hoping to construct a formula that will calculate the total donations for each name in the list and each *similar* name, and then display only the totals that exceed $1,600. Here are the correct answers for the sample data I pasted in this latest thread. The formula would display the following results in column C: A B C Contributor Name Amount Over 1,600? FRENCH KATE K 1550 1650 FRENCH KATHERINE K 100 LINCOLN ELLEN 25 1875 LINCOLN ELLEN M 1000 LINCOLN ELLEN M 800 LINCOLN ELLEN M 50 Column C would be blank in the rest of the rows. The formula would calculate the total donations for *all* names in column A, allowing for slight variations (e.g., ANDREWS KATE and ANDREWS KATE J) but it would only display totals in column C when the total exceeded $1,600. Tell me if I'm still not being clear. I really appreciate your help. "Don Guillett" wrote: What about my answer to your previous post did NOT work?? You need to show examples of the correct answer for a couple of your donors. Either I don't understand or you are NOT explaining yourself well. =IF(SUMPRODUCT(--(LEFT($a$3:$a$3333,3)="abb"),$b$3:$b$33)<1600,"",S UMPRODUCT(--(LEFT($a$3:$a$3333,3)="abb"),$b$3:$b$3333)) In this case LIN would show 1900 -- Don Guillett Microsoft MVP Excel SalesAid Software "t_perkins" wrote in message ... Still hunting for a solution to a problem posted earlier. I have a data table that contains a list of donations, with the name of the donor in column A and the amount of each donation in column B. Some donors gave more than once, and I need to be able to calculate their aggregate donations. In the end, I'm only interested in displaying totals for donors who gave more than $1,600 -- whether through a single donations, or multiple donations. I want to display the totals in column C. What formula will allow me to calculate totals for all donors, including names that are similar but not exact matches (e.g., grouping PERKINS JOHN W together with PERKINS JOHN), and then show totals *only* for donors whose total donations exceed $1,600? Here's some sample data -- in my actual data table, there are thousands of rows, and donors have given anywhere from one to ten or more times. As you can see, the names aren't always entered the same way. My early research indicates that I should maybe use the LEFT function to compare the beginning characters of names, but please tell me if there's another way. A B C Contributor Name Amount Over 1,600? ABBOTT DOROTHY C 1500 ABBOTT DOROTHY C 15 ANDREWS KATE 100 ANDREWS KATE J 50 BROWN MICHAEL 25 DALTON KUMI 1400 FRENCH KATE K 1550 FRENCH KATHERINE K 100 GORDON NANCY 10 LINCOLN EDDIE 25 LINCOLN ELLEN 25 LINCOLN ELLEN M 1000 LINCOLN ELLEN M 800 LINCOLN ELLEN M 50 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate totals for a range of names
Mr. t
It looks to me like you need another column that you can use for grouping purposes. Even with thousands of rows you can probably populate an additional column with a unique name (or donor number) much more easily than you can come up with a formula that can distinguish between all the names and versions of names that people can come up with. If you started by using advance filtering to filter a list of unique names to another location, then put the name (or number) you would want to associate with all versions of that donor's name, then put a new column in your data base and populated it with a VLOOKUP formula to pick up the desired name (or number), I think you would find that you could very easily use SUM, SUMIF, SUMPRODUCT, or a Pivot Table or someother basic tool of your liking to generate the summary that you want. You may find that you can clean up your name list with a few search/replaces, TRIMs, maybe text to columns or some other tools. People in this group do come up with some really good formulas, so maybe you will get one that will work for you; but, I think fixing the source a problem is often better. Good luck. Ken Norfolk, Va On May 6, 8:04*pm, t_perkins wrote: There *will* be duplicate last names, but I'm going to try your suggestion anyway to see if I can understand how your formula works. *I'll report back tomorrow -- it's closing time here. Thanks much! "RagDyer" wrote: If you assume that there are *no duplicate* last names, use TTC (Text To Columns) to create a "helper" column, and have your formula reference that helper column to perform your calculations. With data in say A2 to Bn, select A2 to An, and then, from the Menu Bar, <Data <Text To Columns Click on "Delimited". then <Next, Then click on "Space", then <Next, In the "Data Preview" window, the left most column is selected by default. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create totals for close-matching names? | Excel Worksheet Functions | |||
Calculate totals | Excel Discussion (Misc queries) | |||
calculate totals | Excel Discussion (Misc queries) | |||
totals sheet- need summary of column of names between sheets | Excel Discussion (Misc queries) | |||
Totals sheet - deal with employee names | Excel Worksheet Functions |