Summing with Conditional Formats

[reemoe]

Hello,

I was curious if it were possible to add together cells in a given row, only if they are not highlighted in red (my conditional format for being an outlier data point).

Thanks for the help!
Ryan

 

[Andrew Poulsom]

What's the Conditional Format condition for red?

 

[GorD]

It is possible to add cells that meet a particular conditional format, not by using the colour, but by using the underlying condition(s) within a sumproduct formula.

i.e you can use the same criteria that the conditional format uses within the count ( normally then sumproduct or sumif)


Post some data and your conditional formats if you need help

 

[reemoe]

Thanks for the quick replies. The condition for red is: the cell in question is "not between AR29+AT29 and AR29-AT29" (Column AR is the Average; Column AT is 2 Standard Deviations).

I'll give it a whirl.

 

[Peter_SSs]

reemoe

Welcome to the Mr Excel board!

If I have understood, then you want to add those numbers in the row that ARE between AR29+AT29 and AR29-AT29?

Try

=SUMIF(A1:J1,">="&AR29-AT29)-SUMIF(A1:J1,">"&AR29+AT29)

You can adjust the row range and tweak the 'greater than' and 'greater than or equal to' signs as required