Sum of cells colored using conditional format

**joshuar** · 10-19-2009, 09:07 AM

Hi friends,

I would appreciate a bit of help with my below problem.

I have a table in which I have conditionally formatted certain cells.
Now I want to sum the conditionally formatted cells (that are colored distinctly) both row-wise and column-wise i.e. the sum of colored cells.
I tried applying a vba program for adding the sum of colored cells but it would not work on the conditionally formatted cells.
Could you please guide?

Thanks in advance.

**NBVC** · 10-19-2009, 09:08 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**zbor** · 10-19-2009, 09:08 AM

What's the rule they are conditional formated?
Use that info.

**joshuar** · 10-19-2009, 09:39 AM

Please find attached a dummy file.
I want the columnwise & rowwise sums for coloured cells.

**zbor** · 10-19-2009, 09:42 AM

won't work

**NBVC** · 10-19-2009, 10:25 AM

Try:

=SUMPRODUCT(--($F5:$AL5<>""),--($G5:$AM5=""),--($H$2:$AN$2+60<TODAY()),--($H$2:$AN$2<>""),$F5:$AL5)

copied down

**joshuar** · 10-20-2009, 03:15 AM

Thanks a ton NBVC.
The formula works fine. Would like a short briefing though.
Also why did the sum of colored cells formula not work for conditionally formatted cells.
Thanks,