How to sum set of contiguous appearance values among set of multiple valued duplicates in excel?

[deputat_x]

Dear All! I thought it will be easy to sum up multiple distinct values among duplicates but when I started to do that I stucked finally. My need is far beyond that is why I need your assistance, please.
The scenario: If a text value is duplicated in a specific range of column A (e.g.), I need to sum all the values of column B (e.g.) that are on the same row as the duplicate entry A and list that value in the row where the "last" (down in the column) duplicate entry is situated each time when a series of duplicates entry appear again.
Example:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">
</code>[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]A:1 Neutral[/TD]
[TD]B:1 0[/TD]
[TD]C:1 1[/TD]
[/TR]
[TR]
[TD]A:2 Good[/TD]
[TD]B:2 1[/TD]
[TD]C:2[/TD]
[/TR]
[TR]
[TD]A:3 Good[/TD]
[TD]B:3 1[/TD]
[TD]C:3 2[/TD]
[/TR]
[TR]
[TD]A:4 Bad[/TD]
[TD]B:4 -1[/TD]
[TD]C:4[/TD]
[/TR]
[TR]
[TD]A:5 Bad[/TD]
[TD]B:5 -1[/TD]
[TD]C:5[/TD]
[/TR]
[TR]
[TD]A:6 Bad[/TD]
[TD]B:6 -1[/TD]
[TD]C:6 -3[/TD]
[/TR]
[TR]
[TD]A:7 Good[/TD]
[TD]B:7 1[/TD]
[TD]C:7 1[/TD]
[/TR]
[TR]
[TD]A:8 Bad[/TD]
[TD]B:8 -1[/TD]
[TD]C:8[/TD]
[/TR]
[TR]
[TD]A:9 Bad[/TD]
[TD]B:9 -1[/TD]
[TD]C:9 -2[/TD]
[/TR]
[TR]
[TD]A:10 Good[/TD]
[TD]B:10 1[/TD]
[TD]C:10 [/TD]
[/TR]
[TR]
[TD]A:11 Good[/TD]
[TD]B:11 1[/TD]
[TD]C:11 2[/TD]
[/TR]
[TR]
[TD]A:12 Neutral[/TD]
[TD]B:12 0[/TD]
[TD]C:12 1[/TD]
[/TR]
</tbody>[/TABLE]
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">

</code>As you can see, Column A has sets of multiple valued duplicates and one valued duplicates:

• “Good” is duplicated 2 timesby 2 values and 1 time by 1 value;
• “Bad” is duplicated 1 time * by 3 values and 1 time* by 2 values;
• “Neutral” is duplicated 2 times* by 1 value.

I need to sum ONLYcontiguous appearance values of column B in the rows where the “Neutral” is duplicated in column A ( C:1 answer = 1 and C:12 answer =1), the “Good” in column A ( C:3 answer = 2, C:7 answer =1, and C:11 answer =2 ) and finally for the “Bad” in Column A (C:6 answer=-3, and C:9 answer = -2 ).

Where in column C?

In addition to the above, I need the formula to write the totals of each changing distinct duplicates in the row of each last unique duplicate number, that is:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;"> 1 must be placed in C:1;
2 must be placed in C:3
(-)3 must be placed in C:6;
1 must be placed in C:7;
(-)2 must be placed in C:9;
2 must be placed in C:11; and
1 must be placed in C:12.
</code>

I think that's it. Thank you all.
Kind regards, Robin

 

[mikerickson]

Have you looked at SUMIF

=SUMIF(A:A, "neutral", B:B) will return the sum of entries in columnB, that are on the same row as a cell in column A that contains "neutral"

 

[deputat_x]

Have you looked at SUMIF

=SUMIF(A:A, "neutral", B:B) will return the sum of entries in columnB, that are on the same row as a cell in column A that contains "neutral"

I tried to calculate the way you've recommended but it calculates total of all appearances. I need the formula to write the totals of the duplicates in the row of each LAST (contiguous appearances) unique duplicate values.

 

[mikerickson]

I don't understand what you mean by "LAST (contiguous appearances) unique duplicate values"

In the OP data, what rows does that refer to?

 

[deputat_x]

Look, in your recommeded function (sumif) I get for "Neutral" in C:1, 1. And for "Good" in C:2 and C:3, I get 1 in either field again. I need to get "Totals" in C:3 equal to 2, C:2 should be empty field. Please see my table in Column C there what I exactly need.

 

[Marcelo Branco]

See if this does what you need.
**Note that i added column headers - they are necessary for the formula work correctly***


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Header1​

[/td][td]

Header2​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Neutral​

[/td][td]

0​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Good​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Good​

[/td][td]

1​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Bad​

[/td][td]

-1​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Bad​

[/td][td]

-1​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Bad​

[/td][td]

-1​

[/td][td]

-3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Good​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

Bad​

[/td][td]

-1​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

Bad​

[/td][td]

-1​

[/td][td]

-2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

Good​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

Good​

[/td][td]

1​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

Neutral​

[/td][td]

0​

[/td][td]

1​

[/td][/tr]
[/table]

Formula in C2 copied down
=IF(A2=A3,"",COUNTIF(A2:INDEX(A$1:A1,LOOKUP(2,1/(A$1:A1<>A2),ROW(A$1:A1)-ROW(A$1)+1)),A2)*IF(A2="Bad",-1,1))

Hope this helps

M.

 

[deputat_x]

You are Brilliant! Thank you so so much!

 

[deputat_x]

See if this does what you need.
**Note that i added column headers - they are necessary for the formula work correctly***


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Header1​

[/TD]
[TD]

Header2​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Neutral​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Good​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Good​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Bad​

[/TD]
[TD]

-1​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Bad​

[/TD]
[TD]

-1​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Bad​

[/TD]
[TD]

-1​

[/TD]
[TD]

-3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Good​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

Bad​

[/TD]
[TD]

-1​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

Bad​

[/TD]
[TD]

-1​

[/TD]
[TD]

-2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

Good​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

Good​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

Neutral​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in C2 copied down
=IF(A2=A3,"",COUNTIF(A2:INDEX(A$1:A1,LOOKUP(2,1/(A$1:A1<>A2),ROW(A$1:A1)-ROW(A$1)+1)),A2)*IF(A2="Bad",-1,1))

Hope this helps

M.

You are Brilliant! Thank you so so much!

 

[Marcelo Branco]

You are welcome. Thank you for your kind words.

Glad to help

M.

 

[deputat_x]

You are welcome. Thank you for your kind words.

Glad to help

M.

No words to thank you. I'm happy