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.

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.

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.

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.

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.

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.

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.

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.

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.

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.