Forum Discussion
Trying to select, subtotal, and color selected cells from a column into categories with subtotals
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
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.