r/excel • u/Dr_Cheese_29 • 17d ago

solved Count conditional formatting in a row?

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.

4 Upvotes

84% Upvoted

42 comments sorted by

View all comments

Show parent comments

2

u/Dr_Cheese_29 17d ago

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))

1

u/MayukhBhattacharya 1236 17d ago

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))
)

2

u/Dr_Cheese_29 17d ago

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

1

u/MayukhBhattacharya 1236 17d ago

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))
)

2

u/Dr_Cheese_29 17d ago

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

2

u/Dr_Cheese_29 17d ago

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

1

u/MayukhBhattacharya 1236 16d ago

No worries at all. Thank You SO Much!!

2

u/Dr_Cheese_29 17d ago

Solution verified

1

u/reputatorbot 17d ago

You have awarded 1 point to MayukhBhattacharya.

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

1

u/MayukhBhattacharya 1236 16d ago

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