根据更改的值计算特定数量的是/否 [英] Counting a specific amount of yes/no based on changing value

问题描述

很抱歉，这是一个非常简单的问题，但是我找不到解决方法。我要/想使用计数功能。我有一列包含是和否的列。我想建立一个方程，该方程将根据另一个数字计算 YES 的数量。然后计算否的数量，直到达到＃ YES 。例如

I'm sorry if this is a really simple questions, but I have not been able to find a solution. I want/think I need to use the count function. I have a column that contains YES and NO. I want to set up an equation that will count the number of YES based on another number. Then count the number of NO until reaching the #YES. For example

    YES/NO       Count #YES       NO Result
      YES            3                5
      YES            5                6
      NO             7                7
      NO
      NO
      NO
      NO
      YES
      NO
      YES
      YES
      YES
      NO
      YES

列是/否被赋予
列 Count #YES 被赋予
列没有结果已找到

对于第一次遍历，该函数将在 YES >是/否列。然后计算第一个是是 NO c $ c>。在此示例中有5个 NO 。对于第二次遍历，该函数将计数为5 YES 。然后计算第一个是 c和第五个是否 $ c>。在此示例中有6个 NO 。

For the first go around, the function would count 3 YES in the YES/NO column. Then count how many NO are in between the first YES and the 3rd YES. There are 5 NO in this example. For the second go around, the function would count 5 YES. Then count how many NO are in between the first YES and 5th YES. There are 6 NO in this example.

实际上不会有 Count #YES 列。相反，此值将存储在 A1 中。该值将更改（基于电子表格中的其他条件）。

There wont actually be a Count #YES Column. Instead this value will be stored in say A1. This value will change (based on other criteria in my spreadsheet).

干杯！

推荐答案

我认为应该这样做。您可以将其包装在 IFERROR 中，以解决列中没有足够 YES实例的可能性，例如示例数据中的8个。

I think something like this should do it. You can wrap this in IFERROR to handle the possibility of there not being enough instances of "YES" in the column, for example 8 in your sample data.

=AGGREGATE(15,6,ROW($A$1:$A$14)/($A$2:$A$15="YES"),B2)-B2

这篇关于根据更改的值计算特定数量的是/否的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！