Новости:

Подпишитесь на рассылку новых сообщений форума через службу рассылок: Subscribe.ru

Главное меню

Игнорировать #Value! в Sumproduct

Автор alexexcel, 04.05.2012, 22:02

« назад - далее »

alexexcel

Формула вида =SUMPRODUCT(--(DAY($A$1:$A$28)=1),($B$1:$B$28)). Проблема в том, что $A$1:$A$28 содержит не только даты и пустые ячейки, но и текст, соответственно DAY($A$1:$A$28)=1 выдает в этих случаях #Value! Из-за этого формула не работает. Желательно не заменять sumproduct другой функцией, т.к. там есть еще дополнительные условия (т.е. полная формула имеет вид =SUMPRODUCT(--(DAY($A$1:$A$28)=1), -- ($E$1:$E$28=3),($B$1:$B$28))

MCH

#1
формула массива:
=СУММ(ЕСЛИ(ЕЧИСЛО(A1:A28);(ДЕНЬ(A1:A28)=1))*B1:B28)
=SUM(IF(ISNUMBER(A1:A28),(DAY(A1:A28)=1))*B1:B28)

alexexcel

  Попытался добавить второе условие с помощью AND, но не работает.

MCH

#3
=СУММ(ЕСЛИ(ЕЧИСЛО($A$1:$A$28)*($C$1:$C$28=3);(ДЕНЬ($A$1:$A$28)=D1))*$B$1:$B$28)
=SUM(IF(ISNUMBER($A$1:$A$28)*($C$1:$C$28=3),(DAY($A$1:$A$28)=D1))*$B$1:$B$28)или
=СУММ(ЕСЛИ(ЕЧИСЛО($A$1:$A$28);(ДЕНЬ($A$1:$A$28)=D1))*($C$1:$C$28=3)*$B$1:$B$28)
=SUM(IF(ISNUMBER($A$1:$A$28),(DAY($A$1:$A$28)=D1))*($C$1:$C$28=3)*$B$1:$B$28)

alexexcel

Да нет, эти формулы не работают тоже. Они все ошибаются, когда подходящих условий (строк) больше, чем одно, как в последнем файле, который я выслал, где два 3-го и два 4-го марта.

MCH

в чем именно ошибка?

alexexcel

Это моя ошибка, я не догадался, что это array. Спасибо большое.
А сохранить sumproduct в формуле никак нельзя?

MCH

Цитата: alexexcel от 05.05.2012, 00:22
А сохранить sumproduct в формуле никак нельзя?
попробуйте так:
=SUMPRODUCT((--TEXT($A$1:$A$28,"d,,0,\0")=D1)*($C$1:$C$28=3)*$B$1:$B$28)

alexexcel

=TEXT(A1,"d,,0,\0") возвращает у меня #Value!

Wasilic

А такой вариант не подойдет?
Может и я на что сгожусь ... Если сгодился, можете меня по+благодарить+.

Serge 007

Цитата: alexexcel от 05.05.2012, 01:09
=TEXT(A1,"d,,0,\0") возвращает у меня #Value!
А что это за формат?
Давайте с примером, тогда понятно будет
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

alexexcel

Wasilic
Там два условия, поэтому в таком виде sumif работать не будет. Хотя конечно в целоам да, если сделать дополнительный столбец, как Вы предлагаете, и в дополнение к Вашей проверке добавить еще проверку на второй критерий, то да, sumif легко решит эту задачу. Спасибо.
Я так понял, что с sumproduct моя задача легко не решается.
Serge 007
Пример был в файле, прикрепленном ранее. А что значит этот формат и почему он выдает у меня ошибку - я бы и сам хотел знать.

MCH

Цитата: alexexcel от 05.05.2012, 12:25
А что значит этот формат и почему он выдает у меня ошибку - я бы и сам хотел знать.
В русской локале работает формат: ТЕКСТ(A1;"Д;\0;0;\0")
При этом если в ячейке положительное число (дата), то функция ТЕКСТ вернет значения дня в текстовом формате, во всех остальных случаях - "0"
по крайней мере формула:
=СУММПРОИЗВ((--ТЕКСТ($A$1:$A$28;"Д;;0;\0")=D1)*($C$1:$C$28=3)*$B$1:$B$28)
или
=СУММПРОИЗВ((ТЕКСТ($A$1:$A$28;"Д")=D1&"")*($C$1:$C$28=3)*$B$1:$B$28)
у меня работает

но судя по вашим формулам у Вас не русская локаль (или MAC, или OO, или ГуглДок)
При переводе на англиский язык заменил "Д" на "d", а точку с запятой на запятую, проверить правильность формулы не могу


alexexcel

У меня англоязычный эксель и локаль американская. Похоже работает вариант d с точкой с запятой. Большое спасибо!