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

问题描述

我的表格如下所示

如果team1值小于一般值，那么它有蓝色，
如果team2

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

最合适的公式是什么？

解决方案

EDIT：合并两个答案：

这个公式会做你想要的，假设你把一列向右移动

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

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

此外，在输入时，使用shift + Enter确保它成为数组

---

我强烈建议您首先将列和行切换为更标准化的格式，工作流与您的数据。我的意思是这样的

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

  = SUM（IF（B：B <假设您在B列中有team1，在A列中是general。 
 / 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屋！