Добавление условия в формулу SUM приводит к ошибке # N/A

Question

Я пытаюсь суммировать диапазон на основе нескольких условий.

Когда я вхожу

=SUM(IF((INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>0)*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>""),INDIRECT("CommissionDetail!$AF$2:$AF$"&COUNT(CommissionDetail!$N:$N))/INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N)),0))

формула возвращает действительный результат.

Тем не менее, когда я добавляю условие

LEFT(INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)),SEARCH("(",INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)))-2)="As and When"

следующее:

=SUM(IF((LEFT(INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)),SEARCH("(",INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)))-2)="As and When")*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>0)*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>""),INDIRECT("CommissionDetail!$AF$2:$AF$"&COUNT(CommissionDetail!$N:$N))/INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N)),0))

тогда я получаю ошибку # N/A.

Даже когда я удаляю операцию деления, например

=SUM(IF((LEFT(INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)),SEARCH("(",INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)))-2)="As and When")*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>0)*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>""),INDIRECT("CommissionDetail!$AF$2:$AF$"&COUNT(CommissionDetail!$N:$N)),0))

Я все еще получаю ошибку # N/A.

Когда я делаю подсчет в столбце Z следующим образом:

=COUNT(IF((LEFT(INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)),SEARCH("(",INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)))-2)="As and When")*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>0)*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>""),INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))))

Я получаю действительный результат, поэтому я знаю, что есть, по крайней мере, некоторые ячейки, которые удовлетворяют условию, которое я упоминал выше

Почему тогда я получаю ошибку # N/A при суммировании?

Меня особенно интересует понимание того, почему формула перестает быть действительной после добавления условия, которое выполняется по крайней мере более одного раза и работает в формуле COUNT.

Конечная цель состоит в том, чтобы определить среднюю комиссию, начисляемую в месяц за продукт. Я снял условия, чтобы разделить комиссию по месяцам, чтобы не усложнять и без того запутанные формулы выше. Основанием для использования этих формул массива является то, что они являются динамическими и рассчитываются только один раз в месяц и должны ежемесячно переноситься в новые рабочие книги пользователями, которые не могут добавлять вспомогательные столбцы или интерпретировать работу формул. Использование вспомогательных столбцов приводит меня к моей цели, но, к сожалению, вспомогательные столбцы в данном случае не являются опциями

Есть идеи?

Answer 1

В первой формуле вы использовали COUNT, т.е.

COUNT(CommissionDetail!$N:$N)

но в дополнительной части вы использовали COUNTA, т.е.

COUNTA(CommissionDetail!$N:$N)

Если в столбце N есть какие-либо текстовые значения (например, строка заголовка), то они не будут давать тот же номер (следовательно, ошибка # N/A), потому что COUNT считает только числа, COUNTA считает все записи.

Я предлагаю вам перейти на COUNTA, потому что использование COUNT будет игнорировать последнюю строку, если у вас есть строка заголовка

Вы, вероятно, также можете немного упростить, не думайте, что вам нужна часть SEARCH в новом условии, почему бы просто не искать ячейки, начинающиеся с As и When (..... и вы могли бы использовать IFERROR вместо проверки пробелов или обнуляет в качестве делителей, то есть включает новое условие (и COUNTA повсюду, как предложено), которое будет:

=SUM(IFERROR(IF(LEFT(INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)),13)="As and When (",INDIRECT("CommissionDetail!$AF$2:$AF$"&COUNTA(CommissionDetail!$N:$N))/ INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNTA(CommissionDetail!$N:$N))),0))

подтверждено с помощью CTRL+SHIFT+ENTER

.... и дальнейшее улучшение, чтобы сделать формулу более читабельной, - это определить ваш повторяющийся элемент, т.е.

=COUNTA(CommissionDetail!$N:$N)

в качестве именованного диапазона, например, RowCount, а затем формула снова становится короче, т.е.

=SUM(IFERROR(IF(LEFT(INDIRECT("CommissionDetail!$AL$2:$AL$"&RowCount),13)="As and When (",INDIRECT("CommissionDetail!$AF$2:$AF$"&RowCount)/ INDIRECT("CommissionDetail!$Z$2:$Z$"&RowCount)),0))