Count and add cells in column after last occurrence of series

[lefty365]

Hello,
I have a spreadsheet with lots of columns. Some columns need items counted and some need items added. I'm hoping someone can help me with code to count and add cells in a column after the last occurrence of a series of items.

Let me explain with an example. The code would start with Col A and search down to find the first blank row. It would count the number of items in the series prior to the blank row. In this case, there are 4 blue items. The next series down is red. The blank row would be found and the red items would be counted. There are 9. It would continue to the last row. Col B would be next with the items before the first blank row being added. The total is 11,329. The next blank row would be found and the previous items added. This would equal 24,998. The code would continue to the last row. I hope this makes sense.

Thank you,

Mark

Col A	Col B
blue
blue
blue
blue
red
red
red
red
red
red
red
red
red
green
green
green
green
green
green
brown
brown
brown
yellow
yellow
yellow
yellow
yellow
yellow

<tbody>
[TD="class: xl63, align: right"]2,000[/TD]

[TD="align: right"]2340[/TD]

[TD="align: right"]3500[/TD]

[TD="align: right"]3489[/TD]

[TD="class: xl63, align: right"]2,000[/TD]

[TD="align: right"]2340[/TD]

[TD="align: right"]3500[/TD]

[TD="align: right"]3489[/TD]

[TD="class: xl63, align: right"]2,000[/TD]

[TD="align: right"]2340[/TD]

[TD="align: right"]3500[/TD]

[TD="align: right"]3489[/TD]

[TD="align: right"]2340[/TD]

[TD="class: xl63, align: right"]2,000[/TD]

[TD="align: right"]2340[/TD]

[TD="align: right"]3500[/TD]

[TD="align: right"]3489[/TD]

[TD="align: right"]2340[/TD]

[TD="align: right"]3500[/TD]

[TD="class: xl63, align: right"]2,000[/TD]

[TD="align: right"]2340[/TD]

[TD="align: right"]3500[/TD]

[TD="class: xl63, align: right"]2,000[/TD]

[TD="align: right"]2340[/TD]

[TD="align: right"]3500[/TD]

[TD="align: right"]3489[/TD]

[TD="align: right"]2340[/TD]

[TD="align: right"]3500[/TD]

</tbody>

 

[lrobbo314]

Why don't you just turn your data into a Pivot Table?

Add Color column to the rows section, then add the column b as a sum of the values, and add column b again as a count of the values. Then you could just filter out the blanks.

 

[lefty365]

Thank you very much for your reply. I appreciate it. You opened my eyes to a pivot table solution. I've been working with it and I think its doable. Thank you very much. I wouldn't have tried without your suggestion.

Sincerely,

Mark

 

[lefty365]

I realized why pivot tables are a challenge. I need to run the report regularly and I want to compile it with the least amount of work. Pivot tables will change based on the information changing. The fields won't be consistent month to month. I may be wrong there but I don't know how to do that.

Thanks