How to Sum cells that have been conditionally formatted

**kbaz555** · 08-20-2014, 06:17 PM

Can anyone please explain in layman's terms how to sum a column that has been conditionally formatted.

On the attached spreadsheet Sheet 1 D6:L18 has been conditionally formatted (green text and green font if certain customers appear in column A) - the data acquired by vlookup from a separate workbook. I would like a formula in row 31 to sum only the figures in black font - if at all possible!

I've had a look at cpearson.com about colour functions but it's way over my head.

Any help would be appreciated.

**Paul** · 08-20-2014, 06:39 PM

Hi kbaz,

Rather than testing the conditional format, just sum the column based on the condition that triggered the conditional format.

It appears you're formatting the cells green/green if the value in column A for that row is either W, V and K. So test those cells to see if the rows are not W, V and K, then sum them.

=SUMIFS(D6:D18,$A$6:$A$18,"<>W",$A$6:$A$18,"<>V",$A$6:$A$18,"<>K")

Fill that to the right. In your sample workbook I get 0, 2500, 2500, 350, 450, ... which appears correct.

**kbaz555** · 08-25-2014, 12:24 PM

Paul

You are a star!

Just what I needed and works perfectly.

Thanks for your help.

How to Sum cells that have been conditionally formatted

LinkBack

Thread Tools

Rate This Thread

Display

How to Sum cells that have been conditionally formatted

Re: How to Sum cells that have been conditionally formatted

Re: How to Sum cells that have been conditionally formatted

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] count conditionally formatted cells

How to add value to conditionally formatted cells in excel

Sum conditionally formatted cells

Counting conditionally formatted cells

[SOLVED] Report for Conditionally Formatted Cells

Bookmarks

Bookmarks

Posting Permissions