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

问题描述

我的表格如下图

如果那个月的team1"值小于general"值，那么它是蓝色的，如果当月team2"值小于general"值，则为粉红色，

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,

现在我想计算一年中每一行有多少蓝色和多少粉红色的单元格(单元格 AK3 和 AL3)

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)

最合适的公式是什么?

推荐答案

在这里合并了两个答案:

merged two answers here:

此公式将满足您的要求，假设您将所有内容向右移动一列(在 A 列中添加一个空列):

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)))

这样做的目的是首先查看列中是否有 Team1.然后继续检查下面的数据是否小于前面的数据.重要的是您将最后一个 if 设为 A，将其他设为 B，否则会汇总错误的数据.(对于team2，您必须将最后一个B3:AJ3 更改为A3:AI3)

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)

此外，输入时使用 shift+enter 以确保它成为数组公式.

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)) 

假设您在 B 列中有 team1，在 A 列中有 General.

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

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