答案1
计数
根据多个条件返回一定范围的计数。
Function:
---------
=COUNTIFS(criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])
示例表:
A B C D E
--+---------+---------+---------+---------+---------+-
1 | Account | Count | | Account | Status |
--+---------+---------+---------+---------+---------+-
2 | MNC | 2 | | MNC | Y |
--+---------+---------+---------+---------+---------+-
3 | BLC | | | MNC | N |
--+---------+---------+---------+---------+---------+-
4 | BTS | | | MNC | Y |
--+---------+---------+---------+---------+---------+-
B3 中的公式
我们想要COUNT
行,如果columnD = A2
并且如果Column E = "Y"
=COUNTIFS(D:D,A2,E:E,"Y")
可以将此公式复制到每一行中column B
A B
--+---------+----------------------------+-
1 | Account | Count |
--+---------+----------------------------+-
2 | MNC | =COUNTIFS(D:D,A2,E:E,"Y") |
--+---------+----------------------------+-
3 | BLC | =COUNTIFS(D:D,A3,E:E,"Y") |
--+---------+----------------------------+-
4 | BTS | =COUNTIFS(D:D,A4,E:E,"Y") |
--+---------+----------------------------+-
5 | MSC | =COUNTIFS(D:D,A5,E:E,"Y") |
--+---------+----------------------------+-