Excel：基于细胞计数的条件 [英] Excel: Cell counts based conditions

问题描述

我再次陷入困境，我无法想象如何做到这一点。
基本上，这里是我需要工作的表格的副本。

  ABCDEFG 
 1个月MON TUE WED THU FRI SAT 
 2 Jan是否不适用是是MayBe 
 3二月否否MayBe是N / A否
 4月1月否是MayBe MayBe是否
 5 4月否否MayBe是N / A否
 6五月否是MayBe MayBe是否
 7 Jan是否不适用是是MayBe 
 8二月是是N / AN / A否MayBe 
 9月4日否是MayBe MayBe是否
 10是是否N / A MayBe MayBe 
  

现在，在另一个选项卡上，我想将所有的Yes和MayBe计算在一个单元格为1月和另一个单元格，我想计算所有的No和N / A为1月
相同的方式，我想计算2月和4月...从列A的所有月份。

所以对于1月份，我将计算12，其中包括所有的No和N / A的所有Yes和MayBe，6。

我希望这是有道理的。

有没有办法通过公式来实现？

感谢提前。

解决方案

是的。你需要使用数组公式。显然，您需要更正范围。我已经按照与您相同的方式安排了我的数据，只有较少的列和行。当您输入像这样的数组公式时，您需要键入 CTRL + SHIFT +

  = SUM（IF（$ A $ 2： $ A $ 9 =Jan，IF（$ B $ 2：$ C $ 9 =是，1，0），0））+ 
 SUM（IF（$ A $ 2：$ A $ 9 =Jan ，IF（$ B $ 2：$ C $ 9 =Maybe，1，0），0））
  

这是我本地的例子。

I am again trapped in a situation where I can't figure how to do it. Basically, here is replica of the table I need to work on.

    A           B           C           D           E           F           G
1   Months      MON         TUE         WED         THU         FRI         SAT
2   Jan         Yes         No          N/A         Yes         Yes         MayBe
3   Feb         No          No          MayBe       Yes         N/A         No
4   Jan         No          Yes         MayBe       MayBe       Yes         No        
5   Apr         No          No          MayBe       Yes         N/A         No
6   May         No          Yes         MayBe       MayBe       Yes         No 
7   Jan         Yes         No          N/A         Yes         Yes         MayBe
8   Feb         Yes         Yes         N/A         N/A         No          MayBe
9   Apr         No          Yes         MayBe       MayBe       Yes         No
10              Yes         Yes         No          N/A         MayBe       MayBe

Now, on another tab I want to calculate all the Yes and MayBe in one cell for Jan and on another cell, I want to count all the No and N/A for Jan. Same way I want to count for Feb and Apr... all the months from column A.

so for Jan, I will have count of 12 which includes all the Yes and MayBe and 6 for all the No and N/A.

I hope this make sense.

Is there anyway to achieve this by formula only?

Thanks in advance.

解决方案

Yes. You need to use array formulas. Obviously you'll need to correct the ranges. I have put my data arranged in the same way as you, just less columns and rows. When you input an array formula like this one, you need to type CTRL + SHIFT + ENTER in the cell for it to calculate properly.

=SUM(IF($A$2:$A$9="Jan",IF($B$2:$C$9="Yes", 1, 0), 0)) + 
    SUM(IF($A$2:$A$9="Jan",IF($B$2:$C$9="Maybe", 1, 0), 0))

This has worked on my local example.

这篇关于Excel：基于细胞计数的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！