COUNTIF函数，如果值等于，则重置计数 [英] COUNTIF Function, reset count if value equals

问题描述

我需要在A列中列出每次出现的B列中值 4A的运行计数，但是如果B列中的值等于 2B，则该计数需要重置为0。

I need to list a running count in column A of each occurrence of the value "4A" in column B, but the count needs to reset to 0 if the value in column B equals "2B".

我附上了我的工作簿样本，A列显示了我需要如何操作计数。我愿意使用VB或公式。

I have attached a sample of my workbook, column A shows how I need the count to operate. I am open to using VB or a formula.

或以文本格式：

| A | B  |
+---+----+
| 0 | 2B |
|   | 3A |
| 1 | 4A |
|   | 4B |
|   | 4B |
| 2 | 4A |
|   | 4B |
| 3 | 4A |
|   | 9A |
| 0 | 2B |
|   | 3A |
| 1 | 4A |
|   | 4C |
| 2 | 4A |
|   | 9A |
| 0 | 2B |
|   | 3A |
| 1 | 4A |
|   | 9A |
| 0 | 2B |
|   | 3A |
| 1 | 4A |
|   | 4C |
|   | 9A |
|   | 9B |
|   | 9Z |

推荐答案

编辑：

@Jeeped和@trincot提出了更好的非数组版本。我在这里稍作修改：

@Jeeped and @trincot proposed better non-array versions. I modified them slightly here:

@Jeeped公式：

@Jeeped's formula:

=IF(OR(B1={"2B","4A"}), COUNTIF(INDEX(B:B,AGGREGATE(14, 6, ROW(B$1:B1)/(B$1:B1="2B"), 1)):B1, "4A"),"")

修改后使用 LARGE 而不是 SMALL 和 而不是 TEXT（，）。

After modification it uses LARGE function in the aggregate rather than SMALL and "" instead of TEXT(,).

@trincot的公式：

@trincot's formula:

=IF(OR(B2={"2B","4A"}),(B2="4A")*(1+IFERROR(INDEX(G:G,MATCH(MAX(G$1:OFFSET(G2,-1,0))+1,G$1:OFFSET(G2,-1,0))),0)),"")

与原始版本相比，它被稍微重新排列为 IF-wise，我还替换了 9999 与 MAX（[...]）+ 1 并将其粘贴，以便可以将其粘贴到第一个单元格中并拖动而不是对第一个像元和连续像元使用两个单独的公式。

Compared to original, it's been rearranged slightly 'IF-wise', I also replaced magic 9999 with MAX([...]) + 1 and made it so that you can just paste it in 1st cell and drag down rather than have two separate formulas for first cell and the consecutive ones.

我的原始版本：

我认为它有本也许是最容易理解的方法（？ -我在这里显然有偏见，所以我可能错了），它在物理上是最短的。

I think it has the benefit of perhaps being the easiest to understand (? - I am obviously biased here so I might be wrong) and it's physically the shortest of all.

它的明显缺点是它是数组公式。

It has the obvious drawback of being an array-formula.

在单元格 A1中可以输入：

In cell "A1" you can write:

=IF(OR(B1="2B",B1="4A"),SUM((B$1:B1="4A")*(ROW(B$1:B1)>MAX(ROW(B$1:B1)*(B$1:B1="2B")))),"")

这是一个数组公式，因此您必须

It's an array formula so you have to confirm it with ctrl + shift + enter.

然后您可以将其向下拖动。

Then you can drag it down.

基本上它是数字假设出现的 4A行比最后一行的 2B大，则从当前行到第一行的范围内出现 4A的次数。

Basically it counts number of occurrences of "4A" in a range extending from current row to the first provided that rows of counted "4A" are larger than that last row of "2B".

这篇关于COUNTIF函数，如果值等于，则重置计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！