| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add data if column Match
I need to add data if sites match in one column (C). If they match, then
corresponding cell data from Column (B) are added together and as a sum in Column D. If there is not a duplicate match it would be great if the cell just carried over to Column D. The duplicates are not consistent and single sites are intermixed. Column B is numeric, Column C is text Example B C D 1 s234 1 2 s235 5 s235 7 4 s236 4 5 s237 1 s237 6 Thank you for your input. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add data if column Match
Hi,
Try putting this in D2 and copying it down: =IF(OR(C2="",C3=C2),"",B2+IF(C2=C1,B1)) Where I assume you have titles in row 1. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "JB Akron" wrote: I need to add data if sites match in one column (C). If they match, then corresponding cell data from Column (B) are added together and as a sum in Column D. If there is not a duplicate match it would be great if the cell just carried over to Column D. The duplicates are not consistent and single sites are intermixed. Column B is numeric, Column C is text Example B C D 1 s234 1 2 s235 5 s235 7 4 s236 4 5 s237 1 s237 6 Thank you for your input. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add data if column Match
Assuming titles in row 1
In D2: =IF(C2="","",IF(LOOKUP(2,1/($C$2:$C$9=C2),ROW($C$2:$C$9)-1)=ROW()-1,SUMIF($C$2:$C$9,C2,$B$2:$B$9),"")) Copy down Adjust your range to suit "JB Akron" wrote: I need to add data if sites match in one column (C). If they match, then corresponding cell data from Column (B) are added together and as a sum in Column D. If there is not a duplicate match it would be great if the cell just carried over to Column D. The duplicates are not consistent and single sites are intermixed. Column B is numeric, Column C is text Example B C D 1 s234 1 2 s235 5 s235 7 4 s236 4 5 s237 1 s237 6 Thank you for your input. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add data if column Match
That only works if there are 1 or 2 like items in column C. If there's more
than 2 you'll get incorrect results. What should happen if there is something like this: B....C.....D.....E 1.....x............... 1.....x.....2........ ......................... 2.....x............... 5.....x.....7......9 Should the result be that as shown in column D or that as shown in column E? Or, will that not happen? -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message ... Hi, Try putting this in D2 and copying it down: =IF(OR(C2="",C3=C2),"",B2+IF(C2=C1,B1)) Where I assume you have titles in row 1. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "JB Akron" wrote: I need to add data if sites match in one column (C). If they match, then corresponding cell data from Column (B) are added together and as a sum in Column D. If there is not a duplicate match it would be great if the cell just carried over to Column D. The duplicates are not consistent and single sites are intermixed. Column B is numeric, Column C is text Example B C D 1 s234 1 2 s235 5 s235 7 4 s236 4 5 s237 1 s237 6 Thank you for your input. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add data if column Match
...ROW($C$2:$C$9)-1)=ROW()-1
No need to subtract 1 ....ROW($C$2:$C$9))=ROW() -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... Assuming titles in row 1 In D2: =IF(C2="","",IF(LOOKUP(2,1/($C$2:$C$9=C2),ROW($C$2:$C$9)-1)=ROW()-1,SUMIF($C$2:$C$9,C2,$B$2:$B$9),"")) Copy down Adjust your range to suit "JB Akron" wrote: I need to add data if sites match in one column (C). If they match, then corresponding cell data from Column (B) are added together and as a sum in Column D. If there is not a duplicate match it would be great if the cell just carried over to Column D. The duplicates are not consistent and single sites are intermixed. Column B is numeric, Column C is text Example B C D 1 s234 1 2 s235 5 s235 7 4 s236 4 5 s237 1 s237 6 Thank you for your input. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add data if column Match
Thank you for the help
"T. Valko" wrote: ...ROW($C$2:$C$9)-1)=ROW()-1 No need to subtract 1 ....ROW($C$2:$C$9))=ROW() -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... Assuming titles in row 1 In D2: =IF(C2="","",IF(LOOKUP(2,1/($C$2:$C$9=C2),ROW($C$2:$C$9)-1)=ROW()-1,SUMIF($C$2:$C$9,C2,$B$2:$B$9),"")) Copy down Adjust your range to suit "JB Akron" wrote: I need to add data if sites match in one column (C). If they match, then corresponding cell data from Column (B) are added together and as a sum in Column D. If there is not a duplicate match it would be great if the cell just carried over to Column D. The duplicates are not consistent and single sites are intermixed. Column B is numeric, Column C is text Example B C D 1 s234 1 2 s235 5 s235 7 4 s236 4 5 s237 1 s237 6 Thank you for your input. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add data if column Match
Thanks
"Shane Devenshire" wrote: Hi, Try putting this in D2 and copying it down: =IF(OR(C2="",C3=C2),"",B2+IF(C2=C1,B1)) Where I assume you have titles in row 1. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "JB Akron" wrote: I need to add data if sites match in one column (C). If they match, then corresponding cell data from Column (B) are added together and as a sum in Column D. If there is not a duplicate match it would be great if the cell just carried over to Column D. The duplicates are not consistent and single sites are intermixed. Column B is numeric, Column C is text Example B C D 1 s234 1 2 s235 5 s235 7 4 s236 4 5 s237 1 s237 6 Thank you for your input. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add data if column Match
Thank you that is exactly what I was looking for
"Teethless mama" wrote: Assuming titles in row 1 In D2: =IF(C2="","",IF(LOOKUP(2,1/($C$2:$C$9=C2),ROW($C$2:$C$9)-1)=ROW()-1,SUMIF($C$2:$C$9,C2,$B$2:$B$9),"")) Copy down Adjust your range to suit "JB Akron" wrote: I need to add data if sites match in one column (C). If they match, then corresponding cell data from Column (B) are added together and as a sum in Column D. If there is not a duplicate match it would be great if the cell just carried over to Column D. The duplicates are not consistent and single sites are intermixed. Column B is numeric, Column C is text Example B C D 1 s234 1 2 s235 5 s235 7 4 s236 4 5 s237 1 s237 6 Thank you for your input. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add data if column Match
True, but the sample data was what I was addressing.
-- If this helps, please click the Yes button. Cheers, Shane Devenshire "T. Valko" wrote: That only works if there are 1 or 2 like items in column C. If there's more than 2 you'll get incorrect results. What should happen if there is something like this: B....C.....D.....E 1.....x............... 1.....x.....2........ ......................... 2.....x............... 5.....x.....7......9 Should the result be that as shown in column D or that as shown in column E? Or, will that not happen? -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message ... Hi, Try putting this in D2 and copying it down: =IF(OR(C2="",C3=C2),"",B2+IF(C2=C1,B1)) Where I assume you have titles in row 1. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "JB Akron" wrote: I need to add data if sites match in one column (C). If they match, then corresponding cell data from Column (B) are added together and as a sum in Column D. If there is not a duplicate match it would be great if the cell just carried over to Column D. The duplicates are not consistent and single sites are intermixed. Column B is numeric, Column C is text Example B C D 1 s234 1 2 s235 5 s235 7 4 s236 4 5 s237 1 s237 6 Thank you for your input. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add data if column Match
Hi,
If there are more than two occurances then you could use: =IF(B2="","",IF(COUNTIF(C$2:C$9,C2)=COUNTIF(C$2:C2 ,C2),SUMIF(C$2:C2,C2,B$2:B2),"")) And if there are no blank rows you can shorten this to =IF(COUNTIF(C$2:C$9,C2)=COUNTIF(C$2:C2,C2),SUMIF(C $2:C2,C2,B$2:B2),"") -- If this helps, please click the Yes button. Cheers, Shane Devenshire "JB Akron" wrote: Thank you that is exactly what I was looking for "Teethless mama" wrote: Assuming titles in row 1 In D2: =IF(C2="","",IF(LOOKUP(2,1/($C$2:$C$9=C2),ROW($C$2:$C$9)-1)=ROW()-1,SUMIF($C$2:$C$9,C2,$B$2:$B$9),"")) Copy down Adjust your range to suit "JB Akron" wrote: I need to add data if sites match in one column (C). If they match, then corresponding cell data from Column (B) are added together and as a sum in Column D. If there is not a duplicate match it would be great if the cell just carried over to Column D. The duplicates are not consistent and single sites are intermixed. Column B is numeric, Column C is text Example B C D 1 s234 1 2 s235 5 s235 7 4 s236 4 5 s237 1 s237 6 Thank you for your input. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using Table Column Data in MATCH | Excel Worksheet Functions | |||
| Lookup where column data does not match | Excel Worksheet Functions | |||
| Match data in 2 columns and return data from 3rd column | Excel Worksheet Functions | |||
| counting data in one column and match with data in another column | Excel Discussion (Misc queries) | |||
| how to match data of one column with other | Excel Worksheet Functions | |||
Linear Mode
