Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Подсчет не пустых ячеек с двойным условием - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчет не пустых ячеек с двойным условием (Формулы/Formulas)
Подсчет не пустых ячеек с двойным условием
Tunka-s Дата: Вторник, 08.08.2017, 17:10 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 82
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Здравствуйте. Помогите пожалуйста с формулой. Надо посчитать все непустые ячейки в столбце "Count", если в первом столбце есть "х" и во втором "ааа". Формула считает неправильно, видимо я чего-то не учла.

Код
=+COUNTA(IF((A2:A6="x")*(B2:B6="aaa");C2:C6))
К сообщению приложен файл: Test.xlsx(31Kb)


Сообщение отредактировал Tunka-s - Вторник, 08.08.2017, 17:22
 
Ответить
СообщениеЗдравствуйте. Помогите пожалуйста с формулой. Надо посчитать все непустые ячейки в столбце "Count", если в первом столбце есть "х" и во втором "ааа". Формула считает неправильно, видимо я чего-то не учла.

Код
=+COUNTA(IF((A2:A6="x")*(B2:B6="aaa");C2:C6))

Автор - Tunka-s
Дата добавления - 08.08.2017 в 17:10
Udik Дата: Вторник, 08.08.2017, 17:23 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1589
Репутация: 192 ±
Замечаний: 0% ±

Excel 2016 х 64
так можно
Код

=СУММПРОИЗВ((C2:C6<>"")*(A2:A6="x")*(B2:B6="aaa"))


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com
 
Ответить
Сообщениетак можно
Код

=СУММПРОИЗВ((C2:C6<>"")*(A2:A6="x")*(B2:B6="aaa"))

Автор - Udik
Дата добавления - 08.08.2017 в 17:23
sboy Дата: Вторник, 08.08.2017, 17:24 | Сообщение № 3
Группа: Проверенные
Ранг: Старожил
Сообщений: 1000
Репутация: 253 ±
Замечаний: 0% ±

Excel 2010
Добрый день.
формула массива
Код
=СУММ((A2:A6="x")*(B2:B6="aaa")*(C2:C6<>"")


Сообщение отредактировал sboy - Вторник, 08.08.2017, 17:27
 
Ответить
СообщениеДобрый день.
формула массива
Код
=СУММ((A2:A6="x")*(B2:B6="aaa")*(C2:C6<>"")

Автор - sboy
Дата добавления - 08.08.2017 в 17:24
_Boroda_ Дата: Вторник, 08.08.2017, 17:35 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11512
Репутация: 4736 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Еще вариант
Код
=СЧЁТЕСЛИМН(A:A;"x";B:B;"aaa";C:C;"<>")
К сообщению приложен файл: Test-10-1.xlsx(31Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЕще вариант
Код
=СЧЁТЕСЛИМН(A:A;"x";B:B;"aaa";C:C;"<>")

Автор - _Boroda_
Дата добавления - 08.08.2017 в 17:35
vikttur Дата: Вторник, 08.08.2017, 17:36 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 2468
Репутация: 430 ±
Замечаний: 0% ±

Вам бы все потяжелее :)
Код
=СЧЁТЕСЛИМН(C2:C6;"<>";A2:A6;"x";B2:B6;"aaa")
 
Ответить
СообщениеВам бы все потяжелее :)
Код
=СЧЁТЕСЛИМН(C2:C6;"<>";A2:A6;"x";B2:B6;"aaa")

Автор - vikttur
Дата добавления - 08.08.2017 в 17:36
Tunka-s Дата: Вторник, 08.08.2017, 17:37 | Сообщение № 6
Группа: Пользователи
Ранг: Участник
Сообщений: 82
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Thanks!!! Думала, что СУМ не могу использовать для текстовых данных.
 
Ответить
СообщениеThanks!!! Думала, что СУМ не могу использовать для текстовых данных.

Автор - Tunka-s
Дата добавления - 08.08.2017 в 17:37
vikttur Дата: Вторник, 08.08.2017, 17:39 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 2468
Репутация: 430 ±
Замечаний: 0% ±

А там нет текстовых данных. Там логические условия.
Применяйте, если можете, СЧЕТЕСЛИМН, она именно для таких подсчетов и предназначена и работает быстрее.
 
Ответить
СообщениеА там нет текстовых данных. Там логические условия.
Применяйте, если можете, СЧЕТЕСЛИМН, она именно для таких подсчетов и предназначена и работает быстрее.

Автор - vikttur
Дата добавления - 08.08.2017 в 17:39
Che79 Дата: Вторник, 08.08.2017, 18:08 | Сообщение № 8
Группа: Проверенные
Ранг: Ветеран
Сообщений: 532
Репутация: 92 ±
Замечаний: 0% ±

Excel 2010 RUS
Вам бы все потяжелее
и поизвращённее yes формула массива
Код
{=СЧЁТ(ЕСЛИ(A2:A6="x";ЕСЛИ(B2:B6="aaa";ЕСЛИ(C2:C6<>"";))))}


Делай нормально и будет нормально!
 
Ответить
Сообщение
Вам бы все потяжелее
и поизвращённее yes формула массива
Код
{=СЧЁТ(ЕСЛИ(A2:A6="x";ЕСЛИ(B2:B6="aaa";ЕСЛИ(C2:C6<>"";))))}

Автор - Che79
Дата добавления - 08.08.2017 в 18:08
Tunka-s Дата: Вторник, 08.08.2017, 18:13 | Сообщение № 9
Группа: Пользователи
Ранг: Участник
Сообщений: 82
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
vikttur, Оригинальная формула COUNTIFS. И она не видит в ячейках цифровые значения. Это и исправляю собственно. :)
 
Ответить
Сообщениеvikttur, Оригинальная формула COUNTIFS. И она не видит в ячейках цифровые значения. Это и исправляю собственно. :)

Автор - Tunka-s
Дата добавления - 08.08.2017 в 18:13
vikttur Дата: Вторник, 08.08.2017, 19:59 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 2468
Репутация: 430 ±
Замечаний: 0% ±

Да ну! Покажите, где это она слепотой страдает.
 
Ответить
СообщениеДа ну! Покажите, где это она слепотой страдает.

Автор - vikttur
Дата добавления - 08.08.2017 в 19:59
Tunka-s Дата: Вторник, 08.08.2017, 22:32 | Сообщение № 11
Группа: Пользователи
Ранг: Участник
Сообщений: 82
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
vikttur, Вам весь файл переслать? :) Он намного больше 100Кб. Формула вся, очень длинная и громоздкая. Есть допустим десять листов, формула проверяет, если в первом листе контрольная ячейка заполнена, то надо посчитатЬ все заполненные поля в столбце по двум параметрам, если нет, то проверяет следующую страницу. Т.е. там 9 вложенных "Если" и под каждым если стоит COUNTIFS. Поля в столбце, который надо посчитать, заполняются тремя способами - первый - вручную (текст или цифры), второй - формулы (влукапит с дополнительной страницы), третий - выпадающие списки, они тоже все дополнительно прописаны. Так вот, COUNTIFS не хочет считать поля, если они заполнены цифровыми значениями из выпадающих списков или если если результат влукапа - цифра с соседней страницы. Почему не знаю, но если все значения везде на дополнительных страницах преобразовать в текст, то все работает. Просто там солько разных списков, что я решила заменить COUNTIFS на что-нибудь менее привередливое. SUMPPRODUCT вроде работает.
 
Ответить
Сообщениеvikttur, Вам весь файл переслать? :) Он намного больше 100Кб. Формула вся, очень длинная и громоздкая. Есть допустим десять листов, формула проверяет, если в первом листе контрольная ячейка заполнена, то надо посчитатЬ все заполненные поля в столбце по двум параметрам, если нет, то проверяет следующую страницу. Т.е. там 9 вложенных "Если" и под каждым если стоит COUNTIFS. Поля в столбце, который надо посчитать, заполняются тремя способами - первый - вручную (текст или цифры), второй - формулы (влукапит с дополнительной страницы), третий - выпадающие списки, они тоже все дополнительно прописаны. Так вот, COUNTIFS не хочет считать поля, если они заполнены цифровыми значениями из выпадающих списков или если если результат влукапа - цифра с соседней страницы. Почему не знаю, но если все значения везде на дополнительных страницах преобразовать в текст, то все работает. Просто там солько разных списков, что я решила заменить COUNTIFS на что-нибудь менее привередливое. SUMPPRODUCT вроде работает.

Автор - Tunka-s
Дата добавления - 08.08.2017 в 22:32
_Boroda_ Дата: Вторник, 08.08.2017, 22:46 | Сообщение № 12
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11512
Репутация: 4736 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Покажите саму формулу. Вернее, даже не всю формулу, а кусок с СУММЕСЛИ
Посмотрите вот эту тему, там как раз про то, что Вы сейчас написали
http://www.excelworld.ru/forum/2-34821-1
СУММПРОИЗВ конечно работает, но СУММЕСЛИ намного быстрее. Поэтому нужно попробовать все-таки сделать через нее. Предположу, что у Вас там часть значений заполняются числами, а часть текстовыми числами.
Попробуйте включить фоновую проверку ошибок (Файл - Параметры - Формулы - галку "Включить фоновый поиск ошибок" и галку "Числа, отформатированные как текст...". Если где-то в таблице на числах появятся зеленые треугольнички, то это не числа, а текст


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеПокажите саму формулу. Вернее, даже не всю формулу, а кусок с СУММЕСЛИ
Посмотрите вот эту тему, там как раз про то, что Вы сейчас написали
http://www.excelworld.ru/forum/2-34821-1
СУММПРОИЗВ конечно работает, но СУММЕСЛИ намного быстрее. Поэтому нужно попробовать все-таки сделать через нее. Предположу, что у Вас там часть значений заполняются числами, а часть текстовыми числами.
Попробуйте включить фоновую проверку ошибок (Файл - Параметры - Формулы - галку "Включить фоновый поиск ошибок" и галку "Числа, отформатированные как текст...". Если где-то в таблице на числах появятся зеленые треугольнички, то это не числа, а текст

Автор - _Boroda_
Дата добавления - 08.08.2017 в 22:46
Tunka-s Дата: Среда, 09.08.2017, 08:54 | Сообщение № 13
Группа: Пользователи
Ранг: Участник
Сообщений: 82
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, Доброе утро! Я знаю про зеленые треугольники. Вот с числами в формате ТЕХТ проблемы нет, а с числами в формате числа почему-то проблема. Про фоновый писк ошибок спасибо. Попробую сейчас.
Вот формула (хотя не понимаю как она может помочь прояснить ситуацию. :))
Код
=IF('QS-702C HAWA OCP-Trading Goods'!$I$6>"";COUNTIFS('QS-702C HAWA OCP-Trading Goods'!$F$6:$F$250;"x";'QS-702C HAWA OCP-Trading Goods'!$G$6:$G$250;B10;'QS-702C HAWA OCP-Trading Goods'!$I$6:$I$250;">""");IF('QS-702D HAWA OEM-Trading Goods'!$I$6>"";COUNTIFS('QS-702D HAWA OEM-Trading Goods'!$F$6:$F$250;"x";'QS-702D HAWA OEM-Trading Goods'!$G$6:$G$250;B10;'QS-702D HAWA OEM-Trading Goods'!$I$6:$I$250;">""");IF('QS-702E ROH - Raw Materials'!$I$6>"";COUNTIFS('QS-702E ROH - Raw Materials'!$F$6:$F$252;"x";'QS-702E ROH - Raw Materials'!$G$6:$G$252;B10;'QS-702E ROH - Raw Materials'!$I$6:$I$252;">""");IF('QS-702F HALB - Semi fin. goods'!$I$6>"";COUNTIFS('QS-702F HALB - Semi fin. goods'!$F$6:$F$250;"x";'QS-702F HALB - Semi fin. goods'!$G$6:$G$250;B10;'QS-702F HALB - Semi fin. goods'!$I$6:$I$250;">""");IF('QS-702G FERT - Fin. Goods UK'!$I$6>"";COUNTIFS('QS-702G FERT - Fin. Goods UK'!$F$6:$F$250;"x";'QS-702G FERT - Fin. Goods UK'!$G$6:$G$250;B10;'QS-702G FERT - Fin. Goods UK'!$I$6:$I$250;">""");IF('QS-702G FERT-Fin. Goods Belgium'!$I$6>"";COUNTIFS('QS-702G FERT-Fin. Goods Belgium'!$F$6:$F$250;"x";'QS-702G FERT-Fin. Goods Belgium'!$G$6:$G$250;B10;'QS-702G FERT-Fin. Goods Belgium'!$I$6:$I$250;">""");""))))))


Сообщение отредактировал Tunka-s - Среда, 09.08.2017, 08:55
 
Ответить
Сообщение_Boroda_, Доброе утро! Я знаю про зеленые треугольники. Вот с числами в формате ТЕХТ проблемы нет, а с числами в формате числа почему-то проблема. Про фоновый писк ошибок спасибо. Попробую сейчас.
Вот формула (хотя не понимаю как она может помочь прояснить ситуацию. :))
Код
=IF('QS-702C HAWA OCP-Trading Goods'!$I$6>"";COUNTIFS('QS-702C HAWA OCP-Trading Goods'!$F$6:$F$250;"x";'QS-702C HAWA OCP-Trading Goods'!$G$6:$G$250;B10;'QS-702C HAWA OCP-Trading Goods'!$I$6:$I$250;">""");IF('QS-702D HAWA OEM-Trading Goods'!$I$6>"";COUNTIFS('QS-702D HAWA OEM-Trading Goods'!$F$6:$F$250;"x";'QS-702D HAWA OEM-Trading Goods'!$G$6:$G$250;B10;'QS-702D HAWA OEM-Trading Goods'!$I$6:$I$250;">""");IF('QS-702E ROH - Raw Materials'!$I$6>"";COUNTIFS('QS-702E ROH - Raw Materials'!$F$6:$F$252;"x";'QS-702E ROH - Raw Materials'!$G$6:$G$252;B10;'QS-702E ROH - Raw Materials'!$I$6:$I$252;">""");IF('QS-702F HALB - Semi fin. goods'!$I$6>"";COUNTIFS('QS-702F HALB - Semi fin. goods'!$F$6:$F$250;"x";'QS-702F HALB - Semi fin. goods'!$G$6:$G$250;B10;'QS-702F HALB - Semi fin. goods'!$I$6:$I$250;">""");IF('QS-702G FERT - Fin. Goods UK'!$I$6>"";COUNTIFS('QS-702G FERT - Fin. Goods UK'!$F$6:$F$250;"x";'QS-702G FERT - Fin. Goods UK'!$G$6:$G$250;B10;'QS-702G FERT - Fin. Goods UK'!$I$6:$I$250;">""");IF('QS-702G FERT-Fin. Goods Belgium'!$I$6>"";COUNTIFS('QS-702G FERT-Fin. Goods Belgium'!$F$6:$F$250;"x";'QS-702G FERT-Fin. Goods Belgium'!$G$6:$G$250;B10;'QS-702G FERT-Fin. Goods Belgium'!$I$6:$I$250;">""");""))))))

Автор - Tunka-s
Дата добавления - 09.08.2017 в 08:54
Tunka-s Дата: Среда, 09.08.2017, 09:03 | Сообщение № 14
Группа: Пользователи
Ранг: Участник
Сообщений: 82
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Включила. Ошибок нет. Пустая ячейка - это же не ошибка! Она просто пустая с точки зрения формулы, и то что я вижу там цифру, это видимо формулу не волнует. :)
 
Ответить
СообщениеВключила. Ошибок нет. Пустая ячейка - это же не ошибка! Она просто пустая с точки зрения формулы, и то что я вижу там цифру, это видимо формулу не волнует. :)

Автор - Tunka-s
Дата добавления - 09.08.2017 в 09:03
_Boroda_ Дата: Среда, 09.08.2017, 09:19 | Сообщение № 15
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11512
Репутация: 4736 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
не понимаю как она может помочь прояснить ситуацию

Очень просто - теперь я понимаю, что не понимаю, чем вызвана эта Ваша фраза
vikttur, Вам весь файл переслать?

Ведь Вам достаточно перенести в отдельный файл всего 2 листа - текущий и, например, QS-702C HAWA OCP-Тrading Goods. В текущем оставить одну ячейку с формулой и еще ячейку B10. Во втором оставить 3 столбца - F,G,I. Сократить формулу до одной СУММЕСЛИ по тому листу, который перенесен, положить полученное сюда и задать вопрос - "Почему СУММЕСЛИ не считает значения из ячеек ..., ... и ...?"

Впрочем, я нисколько не настаиваю - это Ваш файл и Ваша формула. Нам просто захотелось Вам помочь, но если Вам это нужно меньше, чем нам, то извините, что мы тут к Вам пристаем.


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
не понимаю как она может помочь прояснить ситуацию

Очень просто - теперь я понимаю, что не понимаю, чем вызвана эта Ваша фраза
vikttur, Вам весь файл переслать?

Ведь Вам достаточно перенести в отдельный файл всего 2 листа - текущий и, например, QS-702C HAWA OCP-Тrading Goods. В текущем оставить одну ячейку с формулой и еще ячейку B10. Во втором оставить 3 столбца - F,G,I. Сократить формулу до одной СУММЕСЛИ по тому листу, который перенесен, положить полученное сюда и задать вопрос - "Почему СУММЕСЛИ не считает значения из ячеек ..., ... и ...?"

Впрочем, я нисколько не настаиваю - это Ваш файл и Ваша формула. Нам просто захотелось Вам помочь, но если Вам это нужно меньше, чем нам, то извините, что мы тут к Вам пристаем.

Автор - _Boroda_
Дата добавления - 09.08.2017 в 09:19
Tunka-s Дата: Среда, 09.08.2017, 10:05 | Сообщение № 16
Группа: Пользователи
Ранг: Участник
Сообщений: 82
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, Спасибо за желание помочь. :) Вот тут файл с одной страницей. Честно говоря, не была уверена, что ошибка сохранится, если это все скопировать в новый файл, собственно поэтому не сделала этого сразу. Но она есть!!! Выделила красным.
К сообщению приложен файл: 7323800.xlsx(78Kb)
 
Ответить
Сообщение_Boroda_, Спасибо за желание помочь. :) Вот тут файл с одной страницей. Честно говоря, не была уверена, что ошибка сохранится, если это все скопировать в новый файл, собственно поэтому не сделала этого сразу. Но она есть!!! Выделила красным.

Автор - Tunka-s
Дата добавления - 09.08.2017 в 10:05
_Boroda_ Дата: Среда, 09.08.2017, 10:30 | Сообщение № 17
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11512
Репутация: 4736 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Вы зеленые треугольнички включали? Если да, то видели, что в I169 (30) треугольник есть, а в I170 (2) его нет? Это значит, что 30 - это текст, а 2 - это число
Любой текст всегда больше "", а вот любое число всегда меньше текста, поэтому в СЧЁТЕСЛИМН число по условию ">""" и не считается
Перепишите не "больше", а "не равно"
Код
СЧЁТЕСЛИМН('QS-702D HAWA OEM-Trading Goods'!$F$6:$F$250;"x";'QS-702D HAWA OEM-Trading Goods'!$G$6:$G$250;B8;'QS-702D HAWA OEM-Trading Goods'!$I$6:$I$250;"<>")
К сообщению приложен файл: 7323800_1.xlsx(78Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеВы зеленые треугольнички включали? Если да, то видели, что в I169 (30) треугольник есть, а в I170 (2) его нет? Это значит, что 30 - это текст, а 2 - это число
Любой текст всегда больше "", а вот любое число всегда меньше текста, поэтому в СЧЁТЕСЛИМН число по условию ">""" и не считается
Перепишите не "больше", а "не равно"
Код
СЧЁТЕСЛИМН('QS-702D HAWA OEM-Trading Goods'!$F$6:$F$250;"x";'QS-702D HAWA OEM-Trading Goods'!$G$6:$G$250;B8;'QS-702D HAWA OEM-Trading Goods'!$I$6:$I$250;"<>")

Автор - _Boroda_
Дата добавления - 09.08.2017 в 10:30
Tunka-s Дата: Среда, 09.08.2017, 10:54 | Сообщение № 18
Группа: Пользователи
Ранг: Участник
Сообщений: 82
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, Вау! Миллион спасибо!!! Очень интересная информация, всегда думала, что >"" означает "не пустой". Теперь мне не надо менять всю формулу!

Всем огромное спасибо!


Сообщение отредактировал Tunka-s - Среда, 09.08.2017, 10:56
 
Ответить
Сообщение_Boroda_, Вау! Миллион спасибо!!! Очень интересная информация, всегда думала, что >"" означает "не пустой". Теперь мне не надо менять всю формулу!

Всем огромное спасибо!

Автор - Tunka-s
Дата добавления - 09.08.2017 в 10:54
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчет не пустых ячеек с двойным условием (Формулы/Formulas)
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2017 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!