如何在小于Excel中的另一个单元格的范围内计数单元格？ [英] How to count cells in a range with a value less than another cell in excel?

问题描述

我的表格如下所示

如果team1值小于该月份的一般值，则为蓝色，
如果team2值为在那个月份小于一般值，那么它有粉红色，

现在我想计算每行中有多少蓝色和多少粉红色的细胞一年（细胞AK3和AL3）

这是最合适的公式是什么？

解决方案

编辑：在这里合并两个答案：

这个公式会做你想要的，假设你把一个列移动到右边列A）中的空列：

  = SUM（IF（C2：AK2 =Team1; IF（C3：AK3 < B3：AJ3; 1; 0）））
  

您在列中有Team1。然后，继续检查以下数据是否小于之前的数据。重要的是你有最后一个如果A，而其他的是B，否则将总结错误的数据。 （对于team2，您将不得不将最后一个B3：AJ3更改为A3：AI3）

此外，当您输入此项时，请使用shift + enter确保它成为一个数组公式。

---

我强烈建议您先将列和行切换到更标准化的形式，以便更轻松工作流与您的数据。我的意思是这样的

我会建议您查看相似问题的答案这里。总而言之，您收集一列数据，然后使用

  = SUM（IF（B：B< A： A; 1; 0））
  

假设您在列B中有team1，在列A中有一般。 / p>

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?

解决方案

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.

这篇关于如何在小于Excel中的另一个单元格的范围内计数单元格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！