Excel COUNTIF диапазон поиска с возвратом # N/A

Question

Я использую Excel для поиска нескольких дней (мои диапазоны) в расписании по отработанным часам (мой HLOOKUP считывает значение под номером отдела) несколькими сотрудниками для разных отделов.

Я придумал следующую формулу, которая работает для большей части моего листа, за исключением случаев, когда значение поисковой ячейки (AB9) не найдено в первом диапазоне ($ B $ 9:$ E $ 10). Для других это работает (если значение не найдено в более поздних диапазонах, оно не возвращает # N/A), только если значение не найдено в первом диапазоне.

=SUM(IF(COUNTIF($B$9:$E$10,AB9),HLOOKUP(AB9,$B$9:$E$10,2,FALSE),""))+(IF(COUNTIF($F$9:$I$10,AB9),HLOOKUP(AB9,$F$9:$I$10,2,FALSE),""))+(IF(COUNTIF($J$9:$M$10,AB9),HLOOKUP(AB9,$J$9:$M$10,2,FALSE),""))+(IF(COUNTIF($N$9:$Q$10,AB9),HLOOKUP(AB9,$N$9:$Q$10,2,FALSE),""))+(IF(COUNTIF($R$9:$U$10,AB9),HLOOKUP(AB9,$R$9:$U$10,2,FALSE),""))+(IF(COUNTIF($V$9:$Y$10,AB9),HLOOKUP(AB9,$V$9:$Y$10,2,FALSE),""))

Может кто-нибудь объяснить мне, почему это не помогает, и если я не должен использовать COUNTIF для этого, что еще я должен использовать и почему.

В идеале моя формула должна искать уникальные числа (отделы) в диапазонах, сообщать о них, а затем ниже перечислять часы, отработанные для каждого, - но это немного выше моего опыта (в настоящее время)! ;)

Answer 1

Формулы Excel обрабатывают специальные значения ошибок # N/A и # DIV/0 иначе, чем другие (реальные) значения. В зависимости от формул, которые вы используете, они возвращаются как результат, независимо от того, где они отображаются. Таким образом, ваш HLOOKUP возвращает # N/A, когда он не может найти значение, и даже если вы пытаетесь пропустить оценку HLOOKUP в операторе IF и возвращаете просто «», Excel все еще оценивает его и возвращает # N/А как только это будет оценено.

Кроме того, поскольку вы работаете со значениями, а не с текстом, возвращаемое значение должно быть 0, а не "".

Вместо внешнего оператора IF в каждой сумме вы должны использовать IFNA следующим образом:

IFNA(HLOOKUP(AB9,$B$9:$E$10,2,FALSE),0)

Формула IFNA возвращает значение в первом параметре, если оно действительно работает, создает значение и возвращает «», если оно не работает.

Предоставленная ссылка содержит образец рабочего листа, чтобы показать, как он работает с VLOOKUP, поэтому вы можете проверить формулу. Исходя из приведенного примера, это именно тот случай, когда вы захотите использовать IFNA.

Итак, если я читаю то, что вы пытаетесь сделать правильно, вся формула должна выглядеть так:

=SUM(IFNA(HLOOKUP(AB9,$B$9:$E$10,2,FALSE),0),IFNA(HLOOKUP(AB9,$F$9:$I$10,2,FALSE),""),IFNA(HLOOKUP(AB9,$J$9:$M$10,2,FALSE),""),IFNA(HLOOKUP(AB9,$N$9:$Q$10,2,FALSE),""),IFNA(HLOOKUP(AB9,$R$9:$U$10,2,FALSE),""),IFNA(HLOOKUP(AB9,$V$9:$Y$10,

Answer 2

Мы с коллегой придумали лучшее решение, чем совместить все IF.

Мы придумали =SUMIF($B7:$AI7,AB9,$B8:$AI8) который работает намного лучше, так как нет необходимости во множественных вложенных IF .

Я расширил его, добавив в него отдел по умолчанию и включив время S (Sick) и H (Holiday), которое добавляет общее количество часов для отдела по умолчанию.

=SUMIF($B7:$AI7,AL7,$B8:$AI8)+IF($AJ7=AL7,SUMIF($B7:$AI7,"S",$B8:$AI8)+SUMIF($B7:$AI7,"H",$B7:$AI7),0)

Я иду дальше, так как в следующий раз будут включены почасовые ставки и сверхурочные. Пожелай мне удачи!