Trying to select, subtotal, and color selected cells from a column into categories with subtotals

[realst8]

Let me start with, "I'm sorry I don't know how to ask for what I am trying to do".  That said, here is what I am trying to do and I lack the experience to do it...

I am trying to subtotal categories of expenses from columns of numbers.  I want to use different colors to identify which items are included into which subtotaled category.  I just need to be able to go through the column, mark each item by category, and need some way to indicate if the cell has already been included in another subtotal.  

Is there a simple way to take the list of cells in the formula and assign a color to them?  I want to be able to see if all cells have been used in the subtotals.

[#2]

Hi @realst8 

Can you post a small example where you mock up about a dozen rows and explain the expected result?

I'm having trouble visualising what you want to achieve. If you subtotal by category, what formula do you use? 

If you create a pivot table and summarize data by category, all rows of the source will be included.

So, please mock up a simplified example and we can take it from there.

[#3]

@Ingeborg Hawighorst 

Here is what I am working with.  I am sure this is very basic for most of you out there.  I am really just subtotaling a couple years worth of credit card statement so they can be input into quickbooks and just need a way to calculate the subtotals and somehow mark which cells have already been used.

John

[#4]

Hello @realst8 

Your formulas are constructed to address specific cells manually. That is of course very cumbersome and hard to maintain. 

An easier way is this: I suggest that you add a column to your data where you classify each entry. Let's say in column D you type words like meals, travel, rental car, materials, etc next to each number of column C. 

To make sure the words are always spelled the same, look into Data Validation. You can use the list of entries in column E to create a data validation with a list of values.

You can check if you have missed any entry this way:. Count the numbers in column C with =Count(C:C) and count the category text entries in column D with =CountA(D:D).

Now you can either build a pivot table with the category names in the Rows and the numbers in the Values area.

Or you can use Sumif() functions. In your example, you could use this formula in cell F2 and copy down:

=Sumif(D:D,E2,C:C)

In words: calculate the sum of all values in column C where column D has the same text as E2.

I've put this into the attached file and randomly assigned some categories into column D. Change them to the correct ones.

Let me know if that works for you.