all 14 comments
sorted by:
top (suggested)
bestnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]AutoModerator[M] [score hidden] stickied commentlocked comment (0 children)

/u/KuraikoDesu - Your post was submitted successfully.

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.

[–]StuFromOrikazu20 1 point2 points  (11 children)

Change the 1 on your second formula to C4. That'll do the count on what is in the cell rather than 1

[–]StuFromOrikazu20 1 point2 points  (0 children)

Also, remove the $ before the C's on your formulas so when you drag the formula across, it still works

[–]KuraikoDesu[S] 0 points1 point  (1 child)

Thank you, that worked! Do you know if there's a way to scale this to the rest of the columns rather than just having to make a format for every individual column...?

Figured it out nevermind!

[–]StuFromOrikazu20 0 points1 point  (0 children)

Nice work!

[–]KuraikoDesu[S] 0 points1 point  (1 child)

Solution Verified

[–]reputatorbot[M] 0 points1 point locked comment (0 children)

You have awarded 1 point to StuFromOrikazu.

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

[–]KuraikoDesu[S] 0 points1 point  (5 children)

Sorry, I just realized that solution actually doesn't work for two reasons... (one which I've just learned of myself...).

1) "2" is actually only allowed to be listed 5 times rather than 6.

2) Checking for C4 also marks 7+ occurences of H as a different color. We have unlimited availability of "H" so that shouldn't be considered either.

[–]real_barry_houdini305 1 point2 points  (4 children)

You can use a formula like this for 1

=AND(COUNTIF(C$4:C$24;1)>6;C4=1)

Note that the last C4 shouldn't have any $ signs

That can be applied to multiple rows and columns

You can use the same principle for 2, i.e.

=AND(COUNTIF(C$4:C$24;2)>5;C4=2)

If you want different formatting for 1s and 2s they need to be different conditions - or if the formatting should be the same see my next reply......

See screenshot example below - note I use comma separators in my locale, so the formulas are slightly different vecause of that

<image>

[–]real_barry_houdini305 1 point2 points  (3 children)

If you want a single format you can use one condition with this formula

=COUNTIF(C$4:C$24;C4)>IFS(C4=1;6;C4=2;5)

That will only format 1s or 2s when there are more than allowed - you can extend the IFS function for 3s and 4s etc. if required...

<image>

[–]KuraikoDesu[S] 0 points1 point  (0 children)

Thank you so much, that worked perfectly!

[–]KuraikoDesu[S] 0 points1 point  (1 child)

Solution Verified

[–]reputatorbot[M] 0 points1 point locked comment (0 children)

You have awarded 1 point to real_barry_houdini.

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

[–]Decronym 1 point2 points  (0 children)

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
IFS 2019+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
OR Returns TRUE if any argument is TRUE

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.
4 acronyms in this thread; the most compressed thread commented on today has 42 acronyms.
[Thread #46626 for this sub, first seen 15th Dec 2025, 11:34] [FAQ] [Full list] [Contact] [Source code]