Sum of Colored Cells

[imerial]

Hi,

I'm trying to auto-sum some numbers in excel. On the spreadsheet I attached, I need to auto-sum the numbers in last month's column (in this case, feb). I need to generate two sums, one for all the white cells in Feb and one for all the red cells in Feb. Keeping in mind that I will be adding new months every month and that the formula needs to keep up, I was thinking of having it find the total column and then go to the previous column, since that will always be last month, then add all the white cells and all the red cells, but I have no clue how to do this or if there's a better way. Does anyone have any advice?

Thanks!

[oeldere]

You can't count it with an "normal" excel formula.

What is the reason you collored it red?

It can be done, by VBA.

[imerial]

The table is actually a pivot table in the spreadsheet. It's set-up to highlight certain rows in red, I'm not sure how. I don't have experience with VBA. Is summing colored rows something simple enough that I would be able to learn it?

[Fett2oo5]

Would it be possible to count these instances from the data the pivot table is using? There has to be some condition to set those cells to red, so I'm thinking use the same condition on the data that is available. Instead of counting based on the results of the pivot table.

[Jonmo1]

The book you posted doesn't have a Pivot Table.
It's just manually entered (and manually colored) data.
In this case, if you have XL2007+ you can use the AutoFilter to FILTER by color.
Then use =SUBTOTAL(109,D:D) to get the SUM of the filtered rows.

[FDibbins]

I agree with Fett, there is probably conditional formatting behind some of the cells - that same criteria could be used to do the calc.

we would need to see the actual table (without any sensitive info) to offer any suggestions though

[imerial]

Unfortunately, I can't post the actual table since it contains customer information. How could I see the conditional formatting that makes the cells red?

[Jonmo1]

On the Home Tab - Conditional Formatting - Manage Rules
Select "Show formatting rules for this worksheet"

[Fett2oo5]

I know there is a way to display all the conditional formatting, but I don't have 2010 so I won't know how to instruct you.

[FDibbins]

make sure you are in 1 of the "red" cells before you do what Jon suggested

[imerial]

I followed the instructions, but no conditional formatting rules come up. I don't think it should make any difference, but it's not just one cell that's highlighted, it's the whole row. I've tried selecting the row too, and I still get nothing.

[Fett2oo5]

Is there any way you can alter the table so you can provide an example?
Perhaps change the customer names to Customer1, Customer2. Someway of making the specific information generic, thus not giving out any sensitive information.
Also it doesn't have to be big, you could cut it down to ten or so rows/columns meaning it doesn't have to be the entire workbook.

[imerial]

It looks like the rows don't have conditional formatting for highlighting, they were highlighted manually. So, basically, It's a vanilla pivot table, with manually highlighted rows.

[FDibbins]

can you change the color manually? if so, then they probably dont have CF, and you will have to try and figure out a way to identify what cells need to be added

[imerial]

I can change it manually.

[FDibbins]

ok, so what is the criteria for determining the change?

[imerial]

The criteria is not based on anything in the spreadsheet. It's manually analyzing the file and seeing if any items need to be colored red, and then manually coloring them, so there's nothing I can use to make the formula recognize which items are red and which are not. I'm wondering if I can add a column that says 'r' on the red lines and then use a sumif() to find the 'r's and pull in the associated amounts.

[Jonmo1]

Did you try this?

The book you posted doesn't have a Pivot Table.
It's just manually entered (and manually colored) data.
In this case, if you have XL2007+ you can use the AutoFilter to FILTER by color.
Then use =SUBTOTAL(109,D:D) to get the SUM of the filtered rows.

[ChemistB]

It's manually analyzing the file and seeing if any items need to be colored red,

But is the "analysis" subjective to objective? i.e. the value is within 3 days of coming due, or the values are < 10 or the values are >30% of cell B or Cell B is greater than Cell A.........

[FDibbins]

Thanks Chemist, you beat me to it

If the selection is purely subjective..."OH, I think I will color that 1 red...and that 1, oh, and this 1 too"...then I guess your only option would be to manually put an indicator somewhere, and then base the sum on that

[FDibbins]

Thanks Chemist, you beat me to it

If the selection is purely subjective..."OH, I think I will color that 1 red...and that 1, oh, and this 1 too"...then I guess your only option would be to manually put an indicator somewhere, and then base the sum on that

[Jonmo1]

Here's an example using the AutoFilter to Filter by color (with pivot table inside the Filter's Range).
The using Subtotal to sum the visible rows.

Filter and Sum by color.xlsx

[imerial]

Jonmo1, the problem with using AutoFilter is I can't have that Pivot table AutoFiltered, everything needs to be visible.

The selection is purely subjective, so there's no basis to go off that could be used in the formula. I tried adding a column with "red" on the highlighted rows to pull data in that way, but the problem is if the pivot table refreshes with new information, the column with "red" in it isn't part of the pivot table, so it ends up staying while the pivot table changes, and the wrong lines end up being referenced.

[Jonmo1]

Well if there's no pattern or condition to which cells are colored (it's purely random)
And using the Filter By Color method is not an option.

That leaves only VBA solutions.

See this article on Chip Pearson's website that may be helpful.
http://www.cpearson.com/Excel/colors.aspx

[imerial]

I've found a row in the input data for the excel pivot that allows me to conditionally highlight cells. Yay!

The issue I'm having now is that when I do conditional highlighting, if I'm using column B to decide if the row should be highlighted, it only highlights the cell in column B. I also need to highlight the cells in columns C and D. How would I do that?

Then I just have to get Sumcolor() working.

[ChemistB]

What are you using as your conditional formatting condition?

[imerial]

There are some rows with _U_ in them, those are supposed to be red. so my rule is: Cell Value contains '_U_' Format Red Applies to =$1:$1048576

[ChemistB]

Okay, let's say your data is in A2:Z500. Select that range

Conditional Formatting> New Rule (clear out your old rules first)
Use formula
=COUNTIF($A2:$Z2,"*U*")>0
Format as red fill
Did that work for you?

[imerial]

It highlights the entire column as red.

[ChemistB]

Hmmmm, should turn any row which has one of your "U" data points in it, to red. See attached.

[imerial]

Didn't work, I'm not sure why. I'll just do it manually.