Function: Count conditional formatting colors using formula IF(AND

**thorblow** · 06-13-2021, 06:59 PM

Hi all,

I have found how to count conditional formatting colors when the conditional format is set with a simple formula like =A3>50 or A3=37... but when I change the formula to the classic IF(AND to change the color when the values are not within specific values, then it doesn't work. Even if I change the simple formula adding decimals for example =A3>50,46 it won't work either. See the attached example please.

Is there a way to make the function count properly the colors from conditional formatting by using the formula

Formula:

=IF(AND(A3>=$M$1;A3<=$M$2);0;1)

as it is in the attached example with green color?

Thank you all in advance for your help!

**wk9128** · 06-13-2021, 07:34 PM

EXCEL seems to have no way to judge the color generated by conditional formatting, it must be done with a formula

Cell J2 array formula

HTML Code:

Cell J3 array formula

HTML Code:

**Pete_UK** · 06-14-2021, 05:39 AM

You can use this array* formula in I3, based on your CF condition for green:

=SUM(IF(($A$3:$G$16>=$M$1)*($A$3:$G$16<=$M$2),0,1))

*Note that an array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter, instead of the usual Enter.

It gives a result of 66.

Hope this helps.

Pete

EDIT: You may need to use semicolons ( ; ) instead of commas ( , ), depending on your regional settings.

**thorblow** · 06-17-2021, 06:04 PM

Wow! I didn't know that existed such a thing!! Why the array formula needs to be confirmed differently than the usual enter? Just wondering if there is a rational answer :D

Anyway, thanks a lot for the help! With the ctrl+shift+enter works perfect!

**wk9128** · 06-18-2021, 09:33 PM

You're Welcome. Thank You for the feedback

Function: Count conditional formatting colors using formula IF(AND

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Function: Count conditional formatting colors using formula IF(AND

Re: Function: Count conditional formatting colors using formula IF(AND

Re: Function: Count conditional formatting colors using formula IF(AND

Re: Function: Count conditional formatting colors using formula IF(AND

Re: Function: Count conditional formatting colors using formula IF(AND

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] conditional formatting for multiple columns and count cells based on colors

[SOLVED] Conditional Formatting to fill Colors using formula

Conditional Formatting Formula By Using Colors In the Cells

Conditional Formatting With An IF Function Displaying Colors

[SOLVED] Formula based off cell colors / conditional formatting?

[SOLVED] Conditional Formatting Colors

Count colors with conditional formatting

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions