Я хочу подсчитать вхождения значений >=13.8 в два столбца, с этим проблем нет - но если оба значения в одной строке >=13.8 я хочу, чтобы они считались как одно совпадение.
Вот пример
Результат должен быть 6
Я знаю, что могу добавить третий столбец с результатом для каждой строки, используя простую формулу и подсчитав значения в этом столбце. Но это довольно массивный документ и много листов, так что если бы можно было сделать расчет по одной формуле, это было бы здорово .. :)
Есть идеи?
Если я правильно понял ваш вопрос, вы можете переформулировать задачу, чтобы: Подсчитать любую строку, в которой один из двух столбцов (или оба) содержит значение >=13.8 .
Вы можете использовать SUMPRODUCT для достижения своей цели (предположим, что данные, которые вы дали, находятся в ячейках с B1 по C7):
=SUMPRODUCT( ( ( ( B1:B7 >= 13.8 ) + ( C1:C7 >= 13.8 ) ) > 0 ) * 1 )
Он оценивает условие для каждого из массивов (поэтому мы получаем массив значений true и false для каждой строки), а затем добавляем их значения. Таким образом, вы получаете массив из 0, 1 и 2 (в зависимости от того, имеет ли ячейка в текущей строке совпадающее значение, одну ячейку или обе). Затем эти значения проверяются (с помощью > 0), и вы снова получаете массив значений true и false который мы умножаем на 1, чтобы получить сумму.
Вы можете следить за выполнением формулы, выбрав « Формулы» > « Аудит формул» > « Оценить формулу» на ленте (подробное объяснение со скриншотами здесь).
Вы можете продолжать использовать COUNTIFS здесь, а именно:
=SUM(COUNTIFS(B:B;{">=",">=","<"}&13,8;C:C;{"<",">=",">="}&13,8))
который, хотя и, возможно, слегка запутанный, но имеет преимущество перед SUMPRODUCT том, что вы можете ссылаться на целые столбцы без ущерба для производительности.
Конечно, эта установка не может быть легко расширена для обработки большего количества критериев. Однако, возможно, стоит рассмотреть в этом случае.
Обратите внимание, что вам может потребоваться изменить разделители в константах массива, чтобы они имели допустимый формат для вашей системы, например:
{">="\">="\"<"}
или возможно:
{">=".">="."<"}
С уважением
Я получил это для вас:
=SUM( COUNTIF( A:A;">=13,8" ) ; COUNTIF( B:B;">=13,8" ) )
- COUNTIFS( A:A;">=13,8" ; B:B;">=13,8" )
Он подсчитывает ячейки, соответствующие вашему условию, в каждом столбце и вычитает количество строк, в которых оба столбца соответствуют условию.