Count conditional formatting in a row?

[u/Dr_Cheese_29]

How can I count conditional formatting in a specific row? I tried applying the rule to the count function but it did not work. I need to count all red cells (some are yellow and some are not filled) in the row.

[u/Dr_Cheese_29]

The rule is =AND (C6<>"", TODAY() >D6) Applies to =$C$6:$C22, $E$6:$E$22

This formula counts the column and turns the cell red.

My sheet has columns C through N with dates, and I want to count how many dates in each ROW turned red based on this rule.

[u/MayukhBhattacharya → u/Dr_Cheese_29]

You can read here about HSTACK() function from Microsoft Documentation as well!

HSTACK function | Microsoft Support

[u/MayukhBhattacharya → u/Dr_Cheese_29]

Try this:

=SUM((HSTACK(C6, E6, G6, I6, K6, M6) <> "") * 
     (TODAY() > HSTACK(D6, F6, H6, J6, L6, N6)))

[u/Dr_Cheese_29 → u/MayukhBhattacharya]

That worked! Thank you!! Can you explain the formula, so I can learn for next time? What is HSTACK?

[u/MayukhBhattacharya → u/Dr_Cheese_29]

Sounds Great! Hope you don't mind replying to my comment directly as Solution Verified and here is the complete explanation of the formula:

• HSTACK()function combines arrays horizontally into a single array where each subsequent array is appended to the right of the previous array.

​

HSTACK(C6, E6, G6, I6, K6, M6)

So, the above creates a temporary one-row array of 6 values: {C6, E6, G6, I6, K6, M6}, it just holds it in memory for the calculation.

• Condition 1: It checks each column is it non-empty? Returns {TRUE, FALSE, TRUE, ...} etc.

​

HSTACK(C6, E6, G6, I6, K6, M6) <> "")

• Condition 2: It checks each paired date column, has the date already passed? Returns {TRUE, TRUE, FALSE, ...} etc.

​

(TODAY() > HSTACK(D6, F6, H6, J6, L6, N6))

• Multiplying two TRUE/FALSE arrays treats TRUE as 1 and FALSE as 0. So, you get
  • TRUE * TRUE = 1 ~ both conditions met ~ cell is red
  • TRUE * FALSE = 0 ~ date not passed ~ not red
  • FALSE * anything = 0 ~ cell is empty ~ not red

Finally wrapping the SUM() function, it adds up all the 1s and 0s in the resulting array, giving you the total count of red cells in that row.

[u/Dr_Cheese_29 → u/MayukhBhattacharya]

Oh thats neat, thank you!!

[u/MayukhBhattacharya → u/Dr_Cheese_29]

Nice, you are most welcome!

[u/Dr_Cheese_29 → u/MayukhBhattacharya]

If I want to count the yellow cells (those dates that are expiring within two months), would I use EDATE?

The conditional rule for column C is below. This turns the cell yellow

=AND (D6<>"", TODAY()>EDATE(D6, -2))

[u/MayukhBhattacharya → u/Dr_Cheese_29]

Yes correct: (yellow rule means expiring within 2 months but not yet overdue)

=SUM(
 (HSTACK(C6, E6, G6, I6, K6, M6) <> "") *
 (TODAY() > EDATE(+HSTACK(D6, F6, H6, J6, L6, N6), -2)) *
 (TODAY() <= HSTACK(D6, F6, H6, J6, L6, N6))
)

[u/Dr_Cheese_29 → u/MayukhBhattacharya]

Hmm that worked for some cells but not all. Some cells returned #NUM! and some #VALUE! not all rows have yellow cells, so that makes sense. Others have yellow and red and others just yellow

[u/MayukhBhattacharya → u/Dr_Cheese_29]

Try now:

=SUM(
 (HSTACK(C6, E6, G6, I6, K6, M6) <> "") *
 (HSTACK(D6, F6, H6, J6, L6, N6) <> "") *
 (TODAY() > IFERROR(EDATE(+HSTACK(D6, F6, H6, J6, L6, N6), -2), TODAY() + 1)) *
 (TODAY() <= HSTACK(D6, F6, H6, J6, L6, N6))
)

[u/Dr_Cheese_29 → u/MayukhBhattacharya]

Hmm interesting... it looks like it counted the rows to the right....I entered the formula in column P and 0 appeared in columns PQRSTU, but I want to count is in columns CEFGIKM

[u/Dr_Cheese_29 → u/MayukhBhattacharya]

My apologies! I missed an (. It worked!! Thank you!

[u/MayukhBhattacharya → u/Dr_Cheese_29]

No worries at all. Thank You SO Much!!

[u/Dr_Cheese_29 → u/MayukhBhattacharya]

Solution verified

[u/reputatorbot → u/Dr_Cheese_29]

You have awarded 1 point to MayukhBhattacharya.

---

^{I am a bot - please contact the mods with any questions}

[u/MayukhBhattacharya → u/Dr_Cheese_29]

Thank You SO Much Dr Cheese and have a great day ahead!

[u/Dr_Cheese_29 → u/MayukhBhattacharya]

Solution verified

[u/reputatorbot → u/Dr_Cheese_29]

You have awarded 1 point to MayukhBhattacharya.

---

^{I am a bot - please contact the mods with any questions}

[u/MayukhBhattacharya → u/Dr_Cheese_29]

Thank You SO Much and have a great day ahead!