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	2,000
blue	2340
blue	3500
blue	3489
red	2,000
red	2340
red	3500
red	3489
red	2,000
red	2340
red	3500
red	3489
red	2340
green	2,000
green	2340
green	3500
green	3489
green	2340
green	3500
brown	2,000
brown	2340
brown	3500
yellow	2,000
yellow	2340
yellow	3500
yellow	3489
yellow	2340
yellow	3500

<tbody>
</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