How to count cells in a range with a value less than another cell in excel?

excel count excel-formula conditional-formatting
alwbtc picture alwbtc · Dec 10, 2013 · Viewed 11.8k times · Source

My Table looks like below

enter image description here

if "team1" value is less than "general" value in that month, then it has blue color, if "team2" value is less than "general" value in that month, then it has pink color,

Now I want to count how many blue colored and how many pink colored cells on each row in a year (cells AK3 and AL3)

What is the most appropriate formula for that?

Answer

Siddharth Rout picture Siddharth Rout · Dec 10, 2013

You can use XL4 macros (Excel formula) to count up cells with different backcolor or even font colour in excel :) See this LINK. For Font color the type_num is 24. And for backcolor we will use 63

  1. Open the Name Manager
  2. Give a name. Say BackColor
  3. Type this formula in Refers To =GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),-1,0)) and click OK

enter image description here

The explanation of =GET.CELL() is mentioned in the above link.

Now let's say your workbook looks like this

enter image description here

Next put this formula in row 2.

=backcolor

enter image description here

Next put =COUNTIF(A2:J2,8) and =COUNTIF(A2:J2,7) in cell C5 and C6 respectively and you will get the total count of colors.

enter image description here

Related questions

Count occurrences of values

I have a column of text values with repeated values. I want to create a new column of unique values (no repeats) and a column with the frequency of each of those values. What's the easiest way to do that? …

Read More
Count non blank rows in a certain range/column Excel

I want to count empty (or non-blank) rows in a given column (or range). Example: I have a column which is spaning over 4 cells width, and each cell has either a single ''x'' or is empty. There is up to 100 …

Read More
C# EPPlus OpenXML count rows

With EPPlus and OpenXML does anyone know the syntax on how to count the rows? Say my worksheet is called "worksheet" int numberRows = worksheet.rows.count()? or worksheet.rows.dimension I'm certainly interested in the answer, but how to find …

Read More