CountIf - an easy way to test certain conditions within a group

Imagine this. You’ve got more than one grouping level, and the lowest grouping level has a calculated column. You’d like to create a conditional formatting rule (for the highest level grouping) which is based on two columns observed in the lower grouping level. Consider the following example:

Let’s say you want to make [Product Type] values appear with a red font if there is any [Product Family] with an “&” in it which has a corresponding [Sum of Revenue] >= $50M.

There are a couple ways to do this, but we’ll go through the simplest way here in this post:

  1. Create an indicator calculation column (under the [Product Type] grouping) using:

CountIf( Contains([Product Family], “&”) AND [Sum of Revenue] >= 50000000 )

This formula will count each row within the grouping where both conditions are satisfied.

  1. Rename and/or hide the indicator column.
  2. Click the paintbrush on the left-hand side of the page, then click “Conditional formatting” followed by “+ Add rule”. Then, set a “custom formula” = [Indicator] > 0 for the conditional “formatting rule”. To find the “custom formula” setting, scroll to the bottom of the “formatting rule” dropdown menu.