# Excel: count number of conditionally formatted rows

I have an excel sheet in which I apply conditional formatting on two columns. The row is only highlighted if the cells in that row meet the condition. Specifically, I want to highlight rows where the content in one column begins with a specific letter and the number in another column is greater than 500. I use this function:

``````=AND(\$B2 > 500, SEARCH("A", \$C2)=1)
``````

The correct rows are highlighted, but is there a way to count the number of highlighted rows?

I tried using the COUNTIF function:

``````=COUNTIF(\$B\$2:\$C\$50, AND(\$B2 > 500, SEARCH("A", \$C2)=1))
``````

But it returns 0. Any suggestions?

I am using Microsoft Excel 2016 for Mac

• – Hellion Jun 27 '18 at 18:05
• Alternative, hacky answer: add your AND condition as a new column, count the number of TRUEs in that column. – Hellion Jun 27 '18 at 18:06
• Unfortunately, that just counted all the rows in the range. I think the problem is that none of my methods is treating the highlighted rows as colored. It's treating it as blank. – cslearner88 Jul 1 '18 at 18:26

Using your specific conditional formatting formula requires an array entered solution: Array enter (Ctrl+Shift+Enter) the following formula in `D2` (don't forget to remove the `{` and `}`):

``````{=SUM((B2:B50>500)*IFERROR((SEARCH("A",C2:C50)=1),FALSE))}
``````

As this is an array entered formula, an array is generated for each of the ranges `B2:B50` and `C2:C50`, with the array values being the results of the respective comparisons for each row.

The key thing to understand is that, when multiplied, a `TRUE` value is converted to a `1`, and a `FALSE` value to a `0`. Thus the array generated by the multiplication of the two arrays will contain elements with a value of `1` if and only if both the `B` column is greater than 500 and the `C` column starts with an "A". (Otherwise, the element value will be a `0`.)

Summing the array leads to the count of the rows where both conditions are true.

Rather that checking to see if the index of the first letter "A" found of the value in the `C` column is "1", a better solution would be to check if the first character is an "A", directly:

``````{=SUM((B2:B50>500)*(LEFT(C2:C50,1)="A"))}
``````

With the formula in this new format, and being aware that the `COUNTIFS()` function can use wildcards, it becomes obvious that a non array entered alternative solution is:

``````=COUNTIFS(B2:B50,">500",C2:C50,"=A*")
``````
• Unfortunately, it still returned 0. I think my rows are not being recognized as being highlighted even though they are highlighted. – cslearner88 Jul 1 '18 at 18:26
• @cslearner88 That is not possible. If the conditional formatting formula you use to highlight the cells is the same as what you posted in your question (`=AND(\$B2 > 500, SEARCH("A", \$C2)=1)`), and the `Applies to` range begins with `\$B\$2`, this answer will work. Please double check if the conditional formatting formula is indeed as you specified, and that the `Applies to` range starts at that cell. If you've manually highlighted the cells, then of course this solution won't work. You can test this by manually setting the fill for all the cells to `No Color`. – robinCTS Jul 1 '18 at 18:41
• I should've been more clear in my question. My sheet has five columns from column A to E. The conditional formatting rules are based on columns B and C, but the "Applies To" range is from column A to E. I think that's where my mistake is. What should I do in this case? – cslearner88 Jul 7 '18 at 18:05
• @cslearner88 No, that's not where your mistake is. It will still work. Try setting up a new worksheet with just the data in `B2:C7` like in my answer. Then select `A2:E7`, with `A2` the active cell, and enter the following for the conditional formatting formula: `=AND(\$B2>500,SEARCH("A",\$C2)=1)`. Then enter any of the solutions in the answer in any cell, for example, `{=SUM((B2:B50>500)*(LEFT(C2:C50,1)="A"))}` in `G4`. If you get a zero, you forgot to array enter the formula. PS I've added a non array entered alternative, and an explanation for the array entered version to my answer. – robinCTS Jul 8 '18 at 3:16