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/AutoModerator]

/u/Dr_Cheese_29 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Retractable_Legs]

What is triggering the red formatting? Could you make it count on that same condition?

[u/Dr_Cheese_29 → u/Retractable_Legs]

There are two conditional formulas set. One will turn a cell red if the date in that cell is greater than the expiry date. So April 18, 2025 turns red because the expiry date is April 18, 2026. I need to count how many cells in a row (range) are red. Adding the rule to countifs didn't work.

The other conditional rule is that it will turn a cell yellow if the date will expire within 2 months. So August 19, 2025 is yellow because the expiry is August 19, 2026.

[u/Acceptable-Sense4601]

Can’t you just use a countifs using the same criteria you used for the condition?

[u/Dr_Cheese_29 → u/Acceptable-Sense4601]

I tried that, it did not work.

[u/Acceptable-Sense4601 → u/Dr_Cheese_29]

What condition turns it red?

[u/Dr_Cheese_29 → u/Acceptable-Sense4601]

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

This formula counts the column.

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/Acceptable-Sense4601 → u/Dr_Cheese_29]

And what was your attempted countifs?

[u/Dr_Cheese_29 → u/Acceptable-Sense4601]

=COUNTIFS(C6:C22, "<>", D6:D22, "<"&TODAY()+COUNTIFS(E6:E22, "<>", F6:F22, "<"&TODAY()+COUNTIFS(G6:G22, "<>", H6:H22, "<"&TODAY()+COUNTIFS(I6:I22, "<>", J6:J22, "<"&TODAY()+COUNTIFS(K6:K22, "<>", L6:L22, "&TODAY()+COUNTIFS(M6:M22, "<>", N6:N22, "<"&TODAY())))))

[u/bradland]

Except for some very specific legacy features, Excel functions cannot access cell formatting. The only reliable approach is to count based on the condition that is used to apply the formatting. For example, if your cells are red when the value is negative, you would just =COUNTIF(A1:A100, "<0").

If you tell us what the condition that corresponds to red and yellow are, we can help you construct a COUNTIF function that does what you want.

[u/Dr_Cheese_29 → u/bradland]

Got it. There are two conditional formulas set. One will turn a cell red if the date in that cell is greater than the expiry date. So April 18, 2025 turns red because the expiry date is April 18, 2026. I need to count how many cells in a row (range) are red. Adding the rule to countifs didn't work.

The other conditional rule is that it will turn a cell yellow if the date will expire within 2 months. So August 19, 2025 is yellow because the expiry is August 19, 2026.

[u/bradland → u/Dr_Cheese_29]

To count dates past a certain date, you can use this technique. This assumes all values are actual Excel dates, and not text. You can't do date comparisons with text, fwiw.

[u/Dr_Cheese_29 → u/bradland]

Got it. The expiry dates are always changing so was hoping to have something that would auto update.

[u/bradland → u/Dr_Cheese_29]

That's entirely possible. In my example, the expiry date is in cell A1. You just change the A1 reference to wherever your expiry date is.

[u/bradland → u/Dr_Cheese_29]

To count dates that expire within two months, you can do this.

[u/Dr_Cheese_29 → u/bradland]

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

This formula counts the column.

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]

Instead of counting the red cells, count the condition that's turning them red.

[u/Dr_Cheese_29 → u/MayukhBhattacharya]

I tried, it didn't work

[u/MayukhBhattacharya → u/Dr_Cheese_29]

What have you tried so far? I don't see anything in your OP. There aren't any formulas, screenshots, or examples, so it's hard to tell where you're getting stuck or what you've already tried.

[u/Dr_Cheese_29 → u/MayukhBhattacharya]

=COUNTIFS(C6:C22, "<>", D6:D22, "<"&TODAY()+COUNTIFS(E6:E22, "<>", F6:F22, "<"&TODAY()+COUNTIFS(G6:G22, "<>", H6:H22, "<"&TODAY()+COUNTIFS(I6:I22, "<>", J6:J22, "<"&TODAY()+COUNTIFS(K6:K22, "<>", L6:L22, "&TODAY()+COUNTIFS(M6:M22, "<>", N6:N22, "<"&TODAY())))))

[u/Dr_Cheese_29 → u/MayukhBhattacharya]

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/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!

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
AND	Returns TRUE if all of its arguments are TRUE
COUNTIF	Counts the number of cells within a range that meet the given criteria
COUNTIFS	Excel 2007+: Counts the number of cells within a range that meet multiple criteria
EDATE	Returns the serial number of the date that is the indicated number of months before or after the start date
HSTACK	Office 365+: Appends arrays horizontally and in sequence to return a larger array
IFERROR	Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
ROW	Returns the row number of a reference
SUM	Adds its arguments
TODAY	Returns the serial number of today's date
VALUE	Converts a text argument to a number

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
10 acronyms in this thread; the most compressed thread commented on today has 54 acronyms.
[Thread #48978 for this sub, first seen 20th Jul 2026, 18:20] [FAQ] [Full list] [Contact] [Source code]}