Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Cells Based on Condition (2 problems)
Hello,
I have a spreadsheet that is used to calculate a persons YTD Gross Income. The data contains the SS#, Name, Addr, HireDate, and YTD Gross. The sheet is set up as such: A B C D E F 1. SS# Name Addr HireDate YTD Gross YTD Total 2. 111111111 Joe Schmoe 40000 3. 222222222 Jane Doe 20000 42000 4. 222222222 Jane Doe 22000 5. 555888999 Pete Moss 60000 6. 123456789 John Public 5000 25000 7. 123456789 John Public 15000 20000 8. 123456789 John Public 5000 The first problem I have is a person can work for the company numerous times a year (for example a student who works during their breaks) and their Name, SS#, etc... will appear on the list multiple times. Currently I am using Conditional Formatting to highlight any duplicate rows with the exception of the first occurrence of the SS# by using this formula in the CF dialog box =COUNTIF($A$2:$A2,A2)1. This works fine when it finds two or more duplicate entries (the first entry is not highlighted). I need to be able to SUM the YTD Gross in cell F which would be the first occurrence for a paticular person. As in the case above for Jane Doe; she worked for the company twice so far this year; the second entry will be highlighted yellow; her two YTD Gross amounts will be added and placed in cell F on the first occurrence for her data. i use this formula to accomplish this =IF(A2=A3,H2+H3,""). The CF and this formula work fine when a person is on the list twice but if they are the list more than twice as with John Public then the CF still works fine but the formula will sum the 2nd and 3rd entries where I need a formula that would sum all 3 entries and place the result in the first occurence of the person. In the case of John Public the 3 entries would be added and the result displayed in cell F6. How can I change my formula to make this work? My second problem is once I have my totals in the YTD Gross Total (Column F) I need to delete all the duplicate entries (the ones highlighted in yellow). I am able to do this using some VBA that I found, but when the duplicates are deleted the formulas in Column F no longer work and a #REF error is displayed. Is there a way I can delete the highlighted rows and still keep the dollar value in column F where it belongs? This is a long winded post and i appreciate the time taken to read through it. If I did not explain the issues clearly please ask and I will reply through the post. Any help in resolving these issues will be greatly appreciated. Thank you, Dave Y |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Cells Based on Condition (2 problems)
Let's say your data is in rows 2 to 10
Try this in F2. =IF(COUNTIF(B$2:B2,B2)=1,SUMPRODUCT(--(B$2:B$10=B2),(E$2:E$10)),NA()) You'll get NA if it's a multiple entry. "Dave Y" wrote: Hello, I have a spreadsheet that is used to calculate a persons YTD Gross Income. The data contains the SS#, Name, Addr, HireDate, and YTD Gross. The sheet is set up as such: A B C D E F 1. SS# Name Addr HireDate YTD Gross YTD Total 2. 111111111 Joe Schmoe 40000 3. 222222222 Jane Doe 20000 42000 4. 222222222 Jane Doe 22000 5. 555888999 Pete Moss 60000 6. 123456789 John Public 5000 25000 7. 123456789 John Public 15000 20000 8. 123456789 John Public 5000 The first problem I have is a person can work for the company numerous times a year (for example a student who works during their breaks) and their Name, SS#, etc... will appear on the list multiple times. Currently I am using Conditional Formatting to highlight any duplicate rows with the exception of the first occurrence of the SS# by using this formula in the CF dialog box =COUNTIF($A$2:$A2,A2)1. This works fine when it finds two or more duplicate entries (the first entry is not highlighted). I need to be able to SUM the YTD Gross in cell F which would be the first occurrence for a paticular person. As in the case above for Jane Doe; she worked for the company twice so far this year; the second entry will be highlighted yellow; her two YTD Gross amounts will be added and placed in cell F on the first occurrence for her data. i use this formula to accomplish this =IF(A2=A3,H2+H3,""). The CF and this formula work fine when a person is on the list twice but if they are the list more than twice as with John Public then the CF still works fine but the formula will sum the 2nd and 3rd entries where I need a formula that would sum all 3 entries and place the result in the first occurence of the person. In the case of John Public the 3 entries would be added and the result displayed in cell F6. How can I change my formula to make this work? My second problem is once I have my totals in the YTD Gross Total (Column F) I need to delete all the duplicate entries (the ones highlighted in yellow). I am able to do this using some VBA that I found, but when the duplicates are deleted the formulas in Column F no longer work and a #REF error is displayed. Is there a way I can delete the highlighted rows and still keep the dollar value in column F where it belongs? This is a long winded post and i appreciate the time taken to read through it. If I did not explain the issues clearly please ask and I will reply through the post. Any help in resolving these issues will be greatly appreciated. Thank you, Dave Y |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Cells Based on Condition (2 problems)
Hi Barb,
Can I suggest an improvement? SUMIF will suffice here as there is only a single criterion, i.e. =IF(COUNTIF(B$2:B2,B2)=1,SUMIF(B$2:B$10,B2,E$2:E$1 0),NA()) "Barb Reinhardt" wrote: Let's say your data is in rows 2 to 10 Try this in F2. =IF(COUNTIF(B$2:B2,B2)=1,SUMPRODUCT(--(B$2:B$10=B2),(E$2:E$10)),NA()) You'll get NA if it's a multiple entry. "Dave Y" wrote: Hello, I have a spreadsheet that is used to calculate a persons YTD Gross Income. The data contains the SS#, Name, Addr, HireDate, and YTD Gross. The sheet is set up as such: A B C D E F 1. SS# Name Addr HireDate YTD Gross YTD Total 2. 111111111 Joe Schmoe 40000 3. 222222222 Jane Doe 20000 42000 4. 222222222 Jane Doe 22000 5. 555888999 Pete Moss 60000 6. 123456789 John Public 5000 25000 7. 123456789 John Public 15000 20000 8. 123456789 John Public 5000 The first problem I have is a person can work for the company numerous times a year (for example a student who works during their breaks) and their Name, SS#, etc... will appear on the list multiple times. Currently I am using Conditional Formatting to highlight any duplicate rows with the exception of the first occurrence of the SS# by using this formula in the CF dialog box =COUNTIF($A$2:$A2,A2)1. This works fine when it finds two or more duplicate entries (the first entry is not highlighted). I need to be able to SUM the YTD Gross in cell F which would be the first occurrence for a paticular person. As in the case above for Jane Doe; she worked for the company twice so far this year; the second entry will be highlighted yellow; her two YTD Gross amounts will be added and placed in cell F on the first occurrence for her data. i use this formula to accomplish this =IF(A2=A3,H2+H3,""). The CF and this formula work fine when a person is on the list twice but if they are the list more than twice as with John Public then the CF still works fine but the formula will sum the 2nd and 3rd entries where I need a formula that would sum all 3 entries and place the result in the first occurence of the person. In the case of John Public the 3 entries would be added and the result displayed in cell F6. How can I change my formula to make this work? My second problem is once I have my totals in the YTD Gross Total (Column F) I need to delete all the duplicate entries (the ones highlighted in yellow). I am able to do this using some VBA that I found, but when the duplicates are deleted the formulas in Column F no longer work and a #REF error is displayed. Is there a way I can delete the highlighted rows and still keep the dollar value in column F where it belongs? This is a long winded post and i appreciate the time taken to read through it. If I did not explain the issues clearly please ask and I will reply through the post. Any help in resolving these issues will be greatly appreciated. Thank you, Dave Y |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Cells Based on Condition (2 problems)
Hi barb and Daddylonglegs,
Thank you so much for your responses. It worked out perfectly. I really appreciate your assistance. Have a great weekend, Dave Y "daddylonglegs" wrote: Hi Barb, Can I suggest an improvement? SUMIF will suffice here as there is only a single criterion, i.e. =IF(COUNTIF(B$2:B2,B2)=1,SUMIF(B$2:B$10,B2,E$2:E$1 0),NA()) "Barb Reinhardt" wrote: Let's say your data is in rows 2 to 10 Try this in F2. =IF(COUNTIF(B$2:B2,B2)=1,SUMPRODUCT(--(B$2:B$10=B2),(E$2:E$10)),NA()) You'll get NA if it's a multiple entry. "Dave Y" wrote: Hello, I have a spreadsheet that is used to calculate a persons YTD Gross Income. The data contains the SS#, Name, Addr, HireDate, and YTD Gross. The sheet is set up as such: A B C D E F 1. SS# Name Addr HireDate YTD Gross YTD Total 2. 111111111 Joe Schmoe 40000 3. 222222222 Jane Doe 20000 42000 4. 222222222 Jane Doe 22000 5. 555888999 Pete Moss 60000 6. 123456789 John Public 5000 25000 7. 123456789 John Public 15000 20000 8. 123456789 John Public 5000 The first problem I have is a person can work for the company numerous times a year (for example a student who works during their breaks) and their Name, SS#, etc... will appear on the list multiple times. Currently I am using Conditional Formatting to highlight any duplicate rows with the exception of the first occurrence of the SS# by using this formula in the CF dialog box =COUNTIF($A$2:$A2,A2)1. This works fine when it finds two or more duplicate entries (the first entry is not highlighted). I need to be able to SUM the YTD Gross in cell F which would be the first occurrence for a paticular person. As in the case above for Jane Doe; she worked for the company twice so far this year; the second entry will be highlighted yellow; her two YTD Gross amounts will be added and placed in cell F on the first occurrence for her data. i use this formula to accomplish this =IF(A2=A3,H2+H3,""). The CF and this formula work fine when a person is on the list twice but if they are the list more than twice as with John Public then the CF still works fine but the formula will sum the 2nd and 3rd entries where I need a formula that would sum all 3 entries and place the result in the first occurence of the person. In the case of John Public the 3 entries would be added and the result displayed in cell F6. How can I change my formula to make this work? My second problem is once I have my totals in the YTD Gross Total (Column F) I need to delete all the duplicate entries (the ones highlighted in yellow). I am able to do this using some VBA that I found, but when the duplicates are deleted the formulas in Column F no longer work and a #REF error is displayed. Is there a way I can delete the highlighted rows and still keep the dollar value in column F where it belongs? This is a long winded post and i appreciate the time taken to read through it. If I did not explain the issues clearly please ask and I will reply through the post. Any help in resolving these issues will be greatly appreciated. Thank you, Dave Y |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating only non-empty cells... | Excel Worksheet Functions | |||
is it possible to make certain cells required based on criteria | Excel Worksheet Functions | |||
Conditional Format based on 3 other cells. | Excel Discussion (Misc queries) | |||
Macro to highlight cells based on content | Excel Worksheet Functions | |||
how do i add in numbers automatically based on adjacent cells cont | Excel Discussion (Misc queries) |