1

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

2

Using your specific conditional formatting formula requires an array entered solution:

Worksheet Screenshot

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.