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

Question

My Table looks like below

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 1

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

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

Now let's say your workbook looks like this

Next put this formula in row 2.

=backcolor

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.

Answer 2

EDIT: merged two answers here:

This formula will do what you are looking for, assuming you move everything one column to the right (adding an empty column in column A):

=SUM(IF(C2:AK2="Team1";IF(C3:AK3 < B3:AJ3;1;0)))

What this does is that it first looks if you have Team1 in the column. It then proceeds to check if the data below is less than the one preceding that. It is important you have the last if as A and the others as B because otherwise it will summarise the wrong data. (For team2 you will have to change the last B3:AJ3 to A3:AI3)

Also, use shift+enter when you enter this to make sure it becomes an array formula.

---

I would highly recommend you switch your columns and rows to a more standardised form first in order to have an easier workflow with your data. I mean something like this

I would then recommend you check out the answer for a similar question here. To summarise, you gather the data in one column and then use a

=SUM(IF(B:B < A:A;1;0)) 

assuming you have team1 in column B and general in column A.

Answer 3

Try this:

in B4 put this formula:

=IF(B2="Team1",IF(B3<A3,1,0),IF(B2="Team2",IF(B3<OFFSET(A3,0,-1),1,0),""))

Then then copy it till AJ4.

Then in AK3 put this formula:

=COUNTIFS($A$2:$AJ$2,"Team1",$A$4:$AJ$4,1)

Similarly in AL3 put this formula.

=COUNTIFS($A$2:$AJ$2,"Team2",$A$4:$AJ$4,1)

Hope this approach works for you.