CODEWITHSUNDEEP
excel
davidjwest
davidjwest

Reputation: 546

Highlight Cells Returning True For Countifs

I have a sheet with 10k+ rows that I get every day. I have about 50 formulae that I run against this sheet to count certain occurrences, and example of this is

=COUNTIFS(B:B,"*.jpg")

But as well as returning the totals I want the sheet to highlight every row that has been counted.

I thought I could maybe to that with conditional formatting but I cannot get it to work.

Thanks for any advice.

Upvotes: 0

Views: 2331

Answers (1)

Forward Ed
Forward Ed

Reputation: 9874

I have attached a snap shot of an example sheet where the rows are colour banded using conditional formatting.

Example Sheet

The rule I used to validate if formatting applies has to equate to TRUE. I test my rule off to the side in cell E1 in this case. I can also copy it across to make sure I have the $ in the right spot. When its doing what I want and evaluating to TRUE or FALSE in the right spots, I edit the cell and copy the formula. I then highlight the range I want to apply it to, in this case A1 to B7 (though technically we can adjust later), and I make sure that A1 is the active cell in the selection. Then I go to the Home Ribbon and select conditional formatting and get the pop up window below:

Formatting Rules

You can select all the formatting options for when things are true by left clicking the "Format..." button.

As per Tim's s comment, select the last one from the rule type list "Use a formula...". in the blank bar below paste your rule that will equate to TRUE or FALSE. so in our case the rule I used was:

=ISNUMBER(SEARCH($C$1,$A1))

Basically I am doing a non case sensitive search in cell A for the criteria in C. IF cell A1 has it, it will return a number. ISNUMBER will check that its a number and return TRUE. If it could not find C1 in cell A1 then it would return an error and ISNUMBER would come out false.

Rule manager

Finally we can see where the rule will apply. So if you have 10K rows, it may be easier to simply type in the number of the last row here instead of selecting it manually, though that would still work.

I created 1 colouring rule for each file type I was searching for.

UPDATE

I am not familiar with how your internal array of {"File","VALID"} works, but I am going to assume that you want to count them if column D is equal to one OR the other AND column F is equal to "G_NOT". In this case since those are the only words in the appropriate cell I will not use the search method and instead just use the following as your formula rule for conditional formatting:

=AND($F1="G_NOT",OR($D1="FILE",$D1="VALID"))

This assumes there is no header row and your data starts in row 1. If you have a header row change change those 1 to 2. Have your left most Cell to your right most cell selected with the left most cell being the active cell. Then go into your conditional formatting. When you are done you can manage your rules, and adjust your range to go to the last row.

Upvotes: 1

Related Questions