# Challenge with summing data in a column based on many and varied consecutive occurences in another column.

#### LLPlates

##### Board Regular
Hi,
Am out of my league again and any help would be greatly appreciated.

Column 'P' contains the number of consecutive occurrences of an event, the function in this cell is =IF(A2>0,N2,"")

Column 'W' contains the data I need to total based on the occurrences.

Column 'X' is the example of what I am trying to achieve.

The work book contains hundreds of rows of data and the occurences can vary from 1 to 30

 L M N O P Q R S T U V W X 1 1 1 # 675 2 2 2 # 175 3 3 3 # 525 4 4 4 # 2300 5 5 5 # 4825 6 6 6 # 850 7 7 7 # 2025 8 8 8 # 4100 15475 9 1 1 1 1 1 # 1500 10 1 1 # 350 11 2 2 # 1575 1925 12 1 1 1 1 1 # 500 13 1 1 2 2 2 # 1450 14 1 1 # 1175 15 2 2 # 3500 16 3 3 # 750 5425 17 1 1 1 1 1 # 2050 18 1 1 2 2 2 # 1025 19 1 1 # 2425 20 1 1 1 1 1 # 1525 21 1 1 2 2 2 # 25 22 1 1 # 1400 23 1 1 1 1 1 # 800 24 1 1 2 2 2 # 850 25 1 1 3 3 3 # 1050 26 1 1 4 4 4 # 325 1375 27 1 1 # 1075 28 2 2 # 325

<tbody>
</tbody>

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Should row 9 read 1500 and row 19 = 2425?

Row 9 should be blank because column P is blank
but row 19 should read 2425 - well spotted. Row 22 should

Row 9 should be blank because column P is blank
but row 19 should read 2425 - well spotted. Row 22 should

Insert a row at the beginning and enter a 0 in X1.

In X2 enter and copy down:

=IF(N2<>"",IF(N3="",SUMIF(\$N\$2:N2,"<>",\$W\$2:W2)-SUM(\$X\$1:X1),""),"")

The formula uses the N range instead of P range for they seem not to differ. If that's an incorrect observation, switch to the latter range.

Thank you, thank you, thankyou!!!!
It works, you have made my day!

Thank you, thank you, thankyou!!!!
It works, you have made my day!

You are welcome. Thanks for providing feedback.

Replies
4
Views
273
Replies
3
Views
202
Replies
0
Views
333
Replies
16
Views
365
Replies
8
Views
139

1,202,905
Messages
6,052,479
Members
444,585
Latest member
Godtymer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back