Sumproduct with Substitute

У меня есть диапазон, содержащий числовые значения и пустые ячейки. Некоторые числовые значения будут иметь * как суффикс.

10* 5 7 9 25* 10 

Когда я делаю SUM (A1: A8), я получаю результат 5 + 7 + 9 + 10 = 31, необходимый результат. Теперь мне также требуется общая сумма независимо от * суффикса. Я пытаюсь решить, используя

 SUMPRODUCT(SUBSTITUTE(A1:A8,"*","")) 

Это работает

 SUMPRODUCT({"10","5","7","","9","","25","10"}) 

И выводит 0, так как все являются текстовыми значениями. Когда я использую

 SUMPRODUCT(value(SUBSTITUTE(A1:A8,"*",""))) 

Это работает

 SUMPRODUCT({10,5,7,#VALUE!,9,#VALUE!,25,10}) 

и, наконец, выдает #Value !. Может ли кто-нибудь помочь мне решить эту проблему? Спасибо за ваше время.

Я предполагаю, что пробелы являются проблемой, потому что, когда вы используете = VALUE (""), вы получаете сообщение об ошибке. Попробуйте соединить ноль с началом SUBSTITUTE, чтобы пробелы становились нулями

=SUMPRODUCT(VALUE(0&SUBSTITUTE(A1:A8,"*","")))

или вы можете использовать +0 вместо VALUE, т. е.

=SUMPRODUCT((0&SUBSTITUTE(A1:A8,"*",""))+0)

Пытаться

 =SUM(VALUE(SUBSTITUTE(A1:A8,"*",""))) 

и введите его с помощью Ctrl + Shift + Enter, а не просто Enter. Это делает его формулой массива и будет обрабатывать диапазон A1:A8 как массив для функции SUBSTITUTE() . Таким образом, SUBSTITUTE() теперь оценивает каждое отдельное значение в A1: A8 отдельно. VALUE() преобразует текст в число, а sum () добавляет все из них.

Edit: Формула =SUMPRODUCT(VALUE(SUBSTITUTE(A1:A8,"*",""))) похоже, работает на меня. (Нормальная формула, а не формула массива).

  • Как ссылаться на значение в ячейке слева в формуле
  • vlookup для несортированных данных не работает
  • В Excel, как я могу избежать повторения большой части формулы только для проверки, является ли возвращаемое значение пустым?
  • Наличие скобок внезапно приводит к разрыву кода
  • Надежный генератор реальных значений для Excel для Office 2007 и Vista SP1
  • почему нельзя фильтровать записи с символом ~ в excel
  • Форма прокрутки до последней строки вместо следующей
  • Без следующей ошибки в макросе электронной почты
  • Excel 2007 - Найдите слово
  • Ошибка 3274 внешняя таблица не в ожидаемом формате
  • Макрос сохраняется в личном кабинете, но не отображается в списке макросов
  • Давайте будем гением компьютера.