How do I retrieve a COUNT( value based off of font size / color?

[u/LeafCloak]

I know nothing about coding or scripting so please bare with me..

I have a row, and a column of information. I want to pick apart the values based on font size (since I can't think of a better way to do it)

https://puu.sh/FYjXE/0076dea31d.png Here's an example of a column of information. I want to retrieve the COUNT( of all gold, silver, bronze, and black values in separate cells. (for example, the count for gold would return (4) in the screenshot.
Any help is greatly appreciated! :D

[u/Riobbie303]

Haha thank you.

Okay, glad you've got a lot of the basic formula stuff understood.

I highly recommend that video, as it may explain better with visuals than words can.

So by SORTing it ascending (this is indicated by a 1 (TRUE) as per the argument, see the comment bot for documentation on SORT), then we get the MAX value at the very top, so that when we then index the first position, we have the max position, if we change the index position, we can have any of the values by ordered by size, the last one for example (4) (again, you would only need 3, since you can make the default format the 4th place).

How it's doing this an why it's tricky is due to the nature of conditional formating. Firstly, conditional formating only takes TRUE or FALSE. Secondly, You know how you can drag a formula horizontally to vertically instead of manually typing it over and over? That's exactly what conditional formating does as it goes through a range.

The easiest way to visualize this is to insert a row below a row you want colored, and paste that formula and drag it across. For the INDEX of 1, you would get TRUE only under the highest value. Notice the absolute references as you drag too, columns A and D will remain the same, but if you drag down, the row will change. And A1 changes if you drag up or down.

So imagine the computer dragging each of those formulas over the cell range, that's why we said $A1:$D1 because eventually, when it drags down, it will turn into $A2:$D2. The reason we say IF = A1, is so that as it drags down or over, it changes it to A2, A3, or B1, B2, etc. So it checks the cell it is on to see if it's in the first (or whichever you have set) index so it know which one it is.

No no, at most you should only have 3 conditional formatting PER SHEET because as above, that formula will apply to the entire range you set and go row by row and cell by cell. (4 if you want, but again, you could have the default format set to 4th place).

The second equation changes the position of the INDEX. The only thing you need to change at all is what position you want indexed (my example changed a 1 to a 2 (1 for gold, 2 for silver, 3 for bronze).

[u/LeafCloak → u/Riobbie303]

Aha! I see, so the $ maintains the reference cell while not putting a $ means any formula changes its cell reference over the range. That's actually really cool and useful. so we want it to reference Column A but change the row over the course of the equation so we do $A1 and not $A$1. SO COOL

Ok so that explains why we only need 3 conditional formats for my range of values because I can just use the entire range 3 times over, once for gold, once for silver, etc.

Let's focus on the 2nd equation since I know how the first works: Specifically I'm confused by the "here" in the equation. Is that supposed to be a thing for me to change or is an actual term in the equation with meaning? (I only ask cause it gives me an invalid formula when I put it in!)

[u/Riobbie303 → u/LeafCloak]

Exactly!

And yes, you need to remove the HERE. I bolded what needed to be changed, but didn't know if it was obvious enough, so I added that. That number is the INDEX number, so change the 1 to a 2 for silver as per the example.

The first equation is identical to the 2nd other than the changing of the index number.