Excel SUMPRODUCT «больше, если» #VALUE! ошибка

У меня возникла проблема при использовании функции SUMPRODUCT для ссылки на таблицы с «больше чем» и «меньше» в значениях СУММЫ. В частности, следующее дает мне «#VALUE!». ошибка.

=SUMPRODUCT(SUMIFS(Perf_Data[Gross Claims Paid (non-cumulative)],Perf_Data[Year (AY/UWY)],"<"&'OUTPUT - S.19.01'!$A24,Perf_Data[Development Year],">="&Perf_Data[Year (AY/UWY)]+15,Perf_Data[Applicable Line of Business],LoB_Selection),Perf_Data[@[Currency check and multiplier]]) 

Приведенный выше код суммирует выплаченные брутто-заявки, если их год AY / UWY ниже определенного года X, год разработки выше, чем год X + 15, и они следуют определенной сфере деятельности. Затем эти валовые претензии должны быть умножены на их валютный множитель – и именно здесь Excel начинает выдавать ошибку значения.

Таблица Perf_Data имеет данные во всех заполненных строках (= диапазоны одного размера), и моя формула работает хорошо (= без ошибок), если годы указаны точно (не больше / меньше), а именно:

 SUMPRODUCT(SUMIFS(Perf_Data[Gross Claims Paid (non-cumulative)],Perf_Data[Year (AY/UWY)],'OUTPUT - S.19.01'!$A27,Perf_Data[Development Year],SUM('OUTPUT - S.19.01'!$A27,'OUTPUT - S.19.01'!C$21),Perf_Data[Applicable Line of Business],LoB_Selection),Perf_Data[@[Currency check and multiplier]]) 

(приведенный выше код суммирует брутто-жалобы, выплаченные в соответствии с конкретным AY / UWY и годом разработки, бизнес-направление

Две общие причины – разный размер массивов, цифры в текстовом формате – нет. Есть какие нибудь идеи как это починить?

Любые конструктивные ответы больше, чем приветствуются!

По просьбе (спасибо за напоминание @ScottHoltzman!), Я отправляю свое решение.

Поскольку решение SUMIFS не сработало, я обратился к SUMPRODUCT как к моему решению. Я нашел, что он используется в старых книгах, сделанных, когда SUMIFS еще не существует, он обычно использовался из-за его возможности с несколькими критериями (что, я уверен, многие из вас помнят).

Изменено не так много параметров; Я просто пошел от этого:

 =SUMPRODUCT(SUMIFS(Perf_Data[Gross Claims Paid (non-cumulative)],Perf_Data[Year (AY/UWY)],"<"&'OUTPUT - S.19.01'!$A24,Perf_Data[Development Year],">="&Perf_Data[Year (AY/UWY)]+15,Perf_Data[Applicable Line of Business],LoB_Selection),Perf_Data[@[Currency check and multiplier]]) 

к этому:

 =SUMPRODUCTPerf_Data[Gross Claims Paid (non-cumulative)],--(Perf_Data[Year (AY/UWY)]<'OUTPUT - S.19.01'!$A24),--(Perf_Data[Development Year]>=Perf_Data[Year (AY/UWY)]+15),--(Perf_Data[Applicable Line of Business]=LoB_Selection),Perf_Data[@[Currency check and multiplier]]) 

Условия превратились в логические проверки; для всех значений в столбце, где условие не выполняется, эта строка умножается на нуль (для этого используется «-» – она ​​превращается в «ЛОЖЬ» логически в «0», а «ИСТИНА» – в «1» Следовательно, столбец значений умножается на 1 в соответствующих (= проверенной логической проверке) строках (которые не меняют их), а в конце умножается на множитель валюты для получения желаемого значения.

В качестве побочного примечания, в то время как другая формула «= СУММИРОВАНИЕ (СУММЫ (Perf_Data [Gross Claims Paid (non-cumulative)], Perf_Data [Year (AY / UWY)],« OUTPUT – S.19.01 »! $ A27, Perf_Data [ Год разработки], SUM ('OUTPUT – S.19.01'! $ A27, 'OUTPUT – S.19.01'! C $ 21), Perf_Data [Применимая бизнес-линия], LoB_Selection), Perf_Data [@ [Валютная проверка и множитель]] ) ", это привело к неправильным номерам. Я предполагаю, что информация, предоставленная формулой SUMIFS, не была правильно связана с валютной проверкой и множителем, поэтому я превратил ее в SUMPRODUCT так же, как описано выше.

Дайте знать, если у вас появятся вопросы!

  • формула excel для формулы X
  • Excel: начинайте с именованного диапазона с самого начала независимо от того, где указан список.
  • Если A1 = A2 и B1> B2, тогда скройте строку 2. Какие формулы / правила использовать и где?
  • Сравните два диапазона в excel (для сравнения не линейно)
  • Как я могу подсчитать количество прибывающих в excel
  • Формула для извлечения ячейки из самого правого столбца с данными (знающий номер строки)
  • Непрерывный граф
  • Могу ли я проверить значения в двух столбцах и всех строках, чтобы добавить условное значение?
  • Как назначить номера строк на основе значений строк - Excel
  • Подсчитывайте, если ячейки имеют один и тот же месяц и год в их дате
  • Столбец (или строка) автозаполнения, ссылающийся на (авторизованный) ряд (или столбец)
  • Interesting Posts

    Используйте VBA для вставки значений в вариант, основанный на совпадающем значении

    Excel возвращает 0 вместо правильного результата

    Использование VBA для проверки того, содержит ли ячейка дату и если она продвигает дату один месяц

    Копирование с листа 1 на лист 2 с условием

    Несколько таблиц данных, экспортированных в несколько листов одного и того же листа excel в java

    Формула для получения последнего значения в столбцах, а затем значение другой ячейки в той же строке

    как добавить косую черту во все содержимое строк в excel?

    Sharepoint – Ссылка на файл, который обновляется динамически?

    создавать офисные файлы из python

    поиск минимального значения между 1 значением и столбцом

    Получите значения ячеек в столбце A, если значения ячеек в столбце F меньше числа

    сравнение данных excel с двух отдельных листов с помощью python / xlrd

    Диапазон ссылок в нескольких вкладках другой книги

    Импорт значений из прикрепления почты в Excel .CSV

    Ошибка при попытке прокрутки файлов, изменения и сохранения

    Давайте будем гением компьютера.