Using Ctrl+F vs Conditional Formatting

Copper Contributor

Somewhat of an odd question here...

 

I have a list of values in an Excel Sheet that contain values like "Successful", "Running", 'Blocked", etc. with "Successful" being the predominant values in the list.  I was to be able to find and highlight any values that don't equal "Successful" in order to call them out - which would normally result in using Conditional Formatting on the column.  Conditional formatting works, however, I also need to apply subtotals based on a secondary column so that I can easily count the number of entries within a timeframe, and Conditional Formatting considers the blank in the subtotal row to also qualify for highlighting.  Is there any way to use the Ctrl+F to "not" find a specific value using the Find-All option?  I'm asking this because the Find All returns specific cell locations, and selecting them all using Ctrl+A enables me to apply formatting to specifically those cells only - and that formatting doesn't take effect when subtotals are applied.

Alternatively - is there any way to apply conditional formatting to a column of data that does not automatically apply to newly inserted rows within the beginning and end of the range that conditional formatting was applied to?

 

My target here is "fewest possible keystrokes" to accomplish calling out specific cells in a range.

 

Thanks,

John

1 Reply

I wonder if the COUNTIFS function will help you out here.  Such as

 

Supposing column C has your status, column B has your month number

=COUNTIFS(C3,"Successful",B3,">"&3,B3,"<"&7)

This formula would count 1 for each "Successful"s in the 2nd quarter.