Excel. Подсчет и суммирование ячеек, отвечающих критерию условного форматирования

Рубрика: 7. Полезняшки Excel

Ранее я описал, как с помощью пользовательской функции найти сумму значений в ячейках, выделенных цветом. К сожалению, эта функция не работает, если ячейки раскрашены с помощью условного форматирования. Я обещал «доработать» функцию. Но за два года, прошедшие с публикации той заметки, я не смог ни самостоятельно, ни с помощью информации из Интернета написать удобоваримый код… (Дополнение от 29 марта 2017 г. Спустя еще пять лет, код мне всё же удалось написать; см. заключительную часть заметки). И вот недавно я наткнулся на идею, содержащуюся в книге Д.Холи, Р. Холи «Excel 2007. Трюки», которая позволяет обойтись вовсе без кода.

Пусть есть список чисел от 1 до 100, размещенных в диапазоне А1:А100 (рис. 1; см. также лист «СУММЕСЛИ» Excel-файла) [1]. На диапазон наложено условное форматирование, помечающее ячейки, содержащие числа больше 10 и меньше или равно 20.

Рис. 1. Диапазон чисел; условным форматированием выделены ячейки, содержащие значения от 10 до 20

Скачать заметку в формате Word, примеры в формате Excel

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

Чтобы сложить диапазон ячеек, отвечающих одному критерию, можно использовать функцию СУММЕСЛИ (рис. 2).

Рис. 2. Суммирование ячеек, отвечающих одному условию

Если у вас несколько условий, можно использовать функцию СУММЕСЛИМН (рис. 3).

Рис. 3. Суммирование ячеек, отвечающих нескольким условиям

Для подсчета числа ячеек, отвечающих одному критерию, можно использовать функцию СЧЁТЕСЛИ.

Для подсчета числа ячеек, отвечающих нескольким критериям, можно использовать функцию СЧЁТЕСЛИМН.

В Excel предусмотрена еще одна функция, которая позволяет указать несколько условий. Эта функция входит в набор функций баз данных Excel и называется БДСУММ. Чтобы проверить ее, используйте тот же набор чисел в диапазоне А2:А100 (рис. 4; см. также лист «БДСУММ» Excel-файла).

Рис. 4. Использование функций баз данных

Выделите ячейки C1:D2 и присвойте этому диапазону имя Критерий, введя его в поле имени слева от строки формул. Теперь выделите ячейку С1 и введите =$А$1, то есть ссылку на первую ячейку на листе, содержащую имя базы данных. Введите =$А$1 в ячейку D1 и вы получите две копии заголовка столбца А. Эти копии будут использоваться как заголовки для условий БДСУММ (C1:D2), который вы назвали Критерий. В ячейке С2 введите >10. В ячейке D2 введите <=20. В ячейке, где должен быть результат, введите следующую формулу:

=БДСУММ($А$1:$А$101,1,Критерий)

Для подсчета числа ячеек, отвечающих нескольким критериям, можно использовать функцию БСЧЁТ.

Дополнение от 29 марта 2017 г.

Читая книгу Джона Уокенбаха Excel 2010. Профессиональное программирование на VBA я узнал, что, начиная с версии Excel 2010 в VBA появилось новое свойство DisplayFormat (см., например, Range.DisplayFormat Property). Т.е., VBA может считывать формат, отображаемый на экране. При этом не важно, как он был получен, прямыми настройками пользователя, или с помощью условного форматирования. К сожалению, разработчики MS сделали так, что свойство DisplayFormat работает только в процедурах, вызываемых из VBA, а пользовательские функции на основе этого свойства выдают ошибку #ЗНАЧ! Тем не менее, получить сумму значений в диапазоне по ячейкам определенного цвета, можно с помощью процедуры (макроса, но не функции). Откройте Excel-файл с примером (содержит код VBA). Пройдите по меню Вид -> Макросы -> Макросы; в окне Макрос, выделите строку СумЦветУсл, и нажмите Выполнить. Запуститься макрос, выберите диапазон суммирования и критерий. Ответ появится в окне.

Код процедуры

Хотя пользовательская функция и дает ошибку, но в процессе ее вызова можно «подсмотреть» ответ. В ячейке начните набирать формулу =su…

Воспользуйтесь подсказкой, кликнув на нее, а затем нажмите знак функции в строке формул:

Введите аргументы, и увидите ответ. К сожалению, нажав, ОК, получите в ячейке значение ошибки.


[1] Массив я создал с помощью функции =СЛУЧМЕЖДУ(1;100)

Комментарии: 137 комментариев

Добрый день.
По работе как раз таки понадобилось подсчитать число ячеек, отвечающих критерию условного форматирования. Критерием является минимальное значение в строке(=B7=МИН($B7:$J7)). Проблема в том, что в данном случае нельзя использовать функцию СЧЕТЕСЛИ, т.к. условие должно быть текстовой строкой.
Посоветуйте, пожалуйста, как можно обойти эту проблему?

Юрий, Вы почему-то решили, что…
> условие должно быть текстовой строкой
Это не так. Вот, что говорится в справке Excel по функции СЧЁТЕСЛИ:
Критерий. Обязательный аргумент. Критерий в форме числа, выражения, ссылки на ячейку или текста или функции, определяющий, какие ячейки необходимо просуммировать.
Так что используйте функцию, что-то типа:
=СЧЁТЕСЛИ($B7:$J7;МИН($B7:$J7))

Мне об этом сообщили на другом сайте:
http://planetaexcel.ru/techniques/2/99/#1523
Возможно, я неправильно понял.
Я как раз предлагал там похожий вариант, но с ошибкой в выражении.
=СЧЁТЕСЛИ($B7:$E7;МИН($B7:$J7)) — функция дала значение «2», такое впечатление она проверила данные только в первой строчке. Смысл в том, чтобы подсчитать количество минимальных значений в строках B7:I7 и т.д. до B30:I30 по каждому столбцу или по четырем, как в моей формуле. Но как бы я не подставлял ссылки, проверяется только первая строка. Не подскажете, в чем у меня ошибка при указании диапазона?

Попробуйте изменить тип ссылок. Возможно, проблема связана с тем, что формула с функцией СЧЁТЕСЛИ некорректно «протягивается». Без реального примера, я не вполне понимаю, чего вы хотите. Пришлите пример мне в личку. Посмотрю…

Требуется посчитать количество ячеек в строке, с условным форматированием равно.

Добрый вечер. Извините, может за глупый вопрос, но никак не могу справиться. Как подсчитать сумму в столбце А текстового значения «В»? Заранее спасибо.

я дополнительный столбец с единичками делал и через суммесли

Добрый день! А есть функция, чтобы можно было просто число заполненных ячеек посчитать, без критериев?

сама уже сообразила))

Добрый день. Есть диапазон D2:D31, в нем есть пары ячеек со значениями 16 и 8 (в сумме сутки), надо подсчитать количество ночных часов (8). Загвоздка в том, что в месяце не всегда попадаются целые пары, например : 8, 16 8, …16 8; или 8, 8, 8, …16 8, 16 8, 8, 8, 8, где 8 после 16 это ночные часы, а просто 8 это выход в день, либо ночные часы с предыдущего месяца.

Задача:
В колонке «I» есть буквенные значения: a, b, c, d…
В колонке «L» соответствуют им числовые значения: 1, 2, 3, 4 …
А как сложить все числовые значения ячеек в колонке L , которые соответствуют значению из любой ячейки (на пример «Z25»), которая соответствует буквенному содержанию (на пример «a») в колонке «I» ???)))

=СУММЕСЛИ(I12:I146;Z25;L12:L146)

добрый день! есть задача
приход денежных средств по дням как суммировать этот приход помесячно? например пришли деньги 10.01.14 — 100т.р 13.01.14- 200т.р 20.01.14 -100 т.р и 03.02.14 500т.р 10.02.14 -700 какие критерии должны задаваться что бы в ячейки январь( другая вкладка) считался весь приход января а февральский приход только в ячейки февраль??

Василий, наверное, лучший вариант, использовать сводную таблицу. Как ее настроить, см. Группировка элементов в отчете сводной таблицы. Но, если Вам обязательно нужны формулы, можно пойти сложным путем и воспользоваться формулами массива, см. Выборка из диапазона дат с помощью критерия в ином формате. Успехов!

Доброго времени суток!
Подскажите пжл, как суммировать количество ячеек с текстом(текст разный), нужно только количество заполненных ячеек. Заранее спасибо.

Анна, воспользуйтесь функцией =СЧЁТЗ(). Эта функция подсчитывает количество непустых ячеек в диапазоне.

помогите пожалуйста. Как посчитать количество (сумму ячеек по нескольким критериям один из которых цвет ячейки. нашёл в интернете надстройку скопировал но там тока по цвету. может добавить в нее что нибудь.

Шама, может быть эта заметка вам поможет: Сумма по цвету ячеек в Excel

Может я не пойму, но по моему у меня другая проблема. У меня есть таблица в ней ячейки с разными значениями. Они отмечены разными цветами в зависимости от ситуации( одно и тоже значение может быть отмечено разными цветами, по этому условное форматирование я не могу использовать) и мне необходимо считать сумму и количество ячеек одного цвета и значения по этому нужны функции типа счетеслимн и сумеслимн но один из критериев это цвет а второй значение.

Шама, добрый вечер, удалось ли решить задачу, у меня такая же проблема. Уже мозг закипает

Посмотрите заметку Сумма по цвету ячеек в Excel, а также обширные комментарии к ней. Может быть, что-то почерпнете…

Доброго времени суток!
Подскажите пожл, как просуммировать суммы
с разными партиями товара.

П9175/P9175, CDN6-93984/2065767

П9175/Р9175, Н-4-097/0018/2082492

Наталья, не очень понятно. Пришлите файл Excel в личку.

Добрый день.
Подскажите, как в строке подсчитать кол-во букв (напр. Я), в одной ячейке — одна буква.

Здравствуйте! Прошу помочь.
Используется: СУММЕСЛИМН(Исходник!D:D;Исходник!A:A;»ПЛАТНЫЕ»;Исходник!B:B;»САМАРА»;Исходник!C:C;»ЛИПЯГИ»), т.е. считается сумма по D:D при условии, что категория пассажира по А:А = ПЛАТНЫЕ и едут от станции В:В = САМАРА, до станции С:C = ЛИПЯГИ.
Вопрос: Надо посчитать сумму по D:D при условии, что А:А равен ПЛАТНЫЕ или УЧАЩИЕСЯ, есть ли другой способ кроме сложения СУММЕСЛИМН(…)+СУММЕСЛИМН(…)?

Здравствуйте! Мне нужна формула, которая считает количество чисел из определенного диапазона, такая есть? Пример выделяю столбец чисел. и мне надо количество чисел от 1 до 5.

Может быть, такая формула подойдет?
=СЧЁТЕСЛИМН(A1:A6;">=1";A1:A6;"<=5")

Спасибо!

Добрый день! Подскажите, пожалуйста, а если мне необходимо, чтобы критерий для поиска, к примеру "<=8", само значение критерия (8) из другой ячейки брал, можно это как-то прописать?

Да. Например, так: =СЧЁТЕСЛИМН(A1:A6;">="&D2; A1:A6;"< ="&D3)
Чтобы увеличить изображение кликните на нем правой кнопкой мыши, и выберите Открыть картинку в новой вкладке

есть массив данных. в каждой строке выделено максимальное значение. Подскажите как рассчитать количество выделенных = максимальных значений в строке по каждому столбцу

Алексей, посмотрите Сумма по цвету ячеек в Excel. Возможно, подскажет вам решение.

Смотрел, но ячейки выделены условным форматированием — и насколько я понял этот метод не работает…

Ну даже не знаю… Если позволяет место, создайте рядом или на другом листе диапазон такого же размера, в котором в каждой ячейке поставьте единицу, если значение соответствует максимуму по строке, и ноль, в противном случае. Далее подсчитайте единицы по столбцам.

спасибо. так уже сделал но думал можно проще…

Здравствуйте.
В формуле =СЧЁТЕСЛИМН(Sluch!$N:$N; F$59; Sluch!$F:$F; $B60) на месте первого условия стоит число. Можно ли как то уместить там же еще 1 критерий равный F$59*2 или придется суммировать с еще одной такой же формулой но с новым условием?

Игорь пришлите файл s_bag@mail.ru, и подробнее опишите, что требуется.

Нашел решение в виде {=СУММ(ЕСЛИ(; 1;0))} и в условии ЕСЛИ уже выставляю то, что мне нужно

Доброго времени суток! Подскажите, пожалуйста, какая нужна формула. Мне нужно суммировать до определенного значения, например, до 200, а потом продолжить суммировать с нуля до иного определенного значения, например, до 300. И при этом не разрывать столбец.

Добрый день. Подскажите пжл, посчитать сумму значений, только по тем значениям где имеется название. заранее спасибо.

Здравствуйте,
Прошу подсказать необходимую функцию формулу по заданному условию:
Существует таблица (ниже) со стандартными товарами, в 1м столбце с повторениями, в зависимости от месяца. В следующих столбцах — наличие товара в различных точках. Как собрать СУММУ по каждому товару (Х) в каждой точке?:
Точка 1, точка2, точка3,… точка99
Товар1
Товар 2
Товар1
Товар 3
Товар1

Сводной таблицей и созданием дополнительного столбца чз функцию суммесли уже делал, но интересно именно прописать все в ОДНОЙ формуле.
Спасибо

Уточнение
Выборку по товарам (1-2-3) необходимо сделать по нескольким столбцам- например, точка 1, точка, 10, точка 20 и точка 30.

Подскажите, пожалуйста как посчитать количество в нескольких столбцах по заданному критерию? (Одно значение с одного столбца легко получаю с помощью впр). а как сумму с нескольких….?
Спасибо заранее 🙂

Елена, придумал решение с помощью функции ДВССЫЛ. Не могу сказать, что оно изящное… но работает.
=СУММ(ДВССЫЛ("B"&ПОИСКПОЗ(C10;A1:A8;ЛОЖЬ)& ":"&"E"&ПОИСКПОЗ(C10;A1:A8;ЛОЖЬ)))
Если вы не знакомы с этой функцией, почитайте Примеры использования функции ДВССЫЛ (INDIRECT)

(Чтобы увеличить изображение, кликните на нем правой кнопкой мыши, и выберите опцию Открыть картинку в новой вкладке)
См. также Excel-файл

Подскажите пожалуйста, как посчитать сумму кратных 3-м, имея столбец неких чисел

Ирина, допустим числа расположены в столбце А. Используйте формулу
=СУММПРОИЗВ(A:A*(ОСТАТ(A:A;3)=0))

Спасибо Вам большое

Добрый вечер, спасибо, очень полезный марериал. «А у вас есть такой же, только с перламутровыми пуговицами?»))
Подскажите пожалуйста, возможно ли суммирование только тех чисел из колонки B, которым в строке A соответствует текст выровнянный слева С ОТСТУПОМ 2 ЗНАКА (а без отступа чтобы не суммировал)? То есть может ли программа различать по такому критерию?
Спасибо заранее!

Сергей, если отступ реализован с помощью нескольких пробелов, то подойдет формула
=СУММЕСЛИ(A1:A2;" *";B1:B2)
Здесь перед звездочкой стоят два пробела.

См. также Excel-файл

Добрый день! Подскажите пожалуйста, как преобразовать определённые числа, а именно числа в диапазоне от 0 до 98,4 в о, а от 98,5 и выше в (например) 15. Выручите пожалуйста.

Например, так: =ЕСЛИ(A1<98,5;0;15). Имеется ввиду, что значение которое нужно преобразовать, находится в ячейке А1.

Подскажите, пожалуйста, как правильно просуммировать платежи за месяцы (узнать сколько заплатили, скажем, в марте определённого года). В одной колонке корректно введённые даты, в другой суммы платежа. Даты сортировать нельзя.

Excel 2002, даты идут вразнобой.
Или хотя бы синтаксис употребления дат в логических формулах (если такое вообще предусмотрено). Заранее большое спасибо

Лион, на мой взгляд, наилучший вариант — использовать сводную таблицу. Альтернатива — функция СУММЕСЛИМН. Здесь 42005 соответствует 1 января 2015 г., а 42035 — 31 января 2015 г.

См. также Excel-файл.

Добрый день.
Возможно есть все же вариант формулы СУММЕСЛИМН, которая ищет значение в диапазоне условий с форматом ячейки «ДАТА».
То есть — колонка даты, формат дата (диапазон условий).
Колонка — суммы оплаты — формат числовой (диапазон суммирования)

Нужна формула, которая считает оплаты по месяцам
*01* *02* и т.д.
когда задаю условие по части содержимого ячейки и указываю между звездочками только месяц — не работает формула. Нулевое значение суммы.
Если перевести в текстовый формат колонку дат — то работает. Но это не подходит — есть требования регламента для дальнейших расчетов по таблице.

Игорь, почитайте Выборка из диапазона дат с помощью критерия в ином формате. Может быть, найдете решение вашей проблемы…

Доброго времени суток!
Работаю с формулой СУММЕСЛИМН, подскажите, пожалуйста, есть ли возможность задать условие для диапазона ячеек с частичным содержанием текста?
Например нужно подсчитать металл толщиной 4 мм, но разных размеров:
-4(50*100)
-4(120х200)

Спасибо!

+ в версии 2002 года этот отчёт не работает (только просмотр)
(

Большое спасибо. Попробовал оба способа:
По первому способу:
Сделал PivotTable, но получилось, что просуммировалось количество чеков в периодах, а не суммы платежей. Подскажите, пожалуйста, как с ней управляться.
По второму способу:
не смог найти таблицу соответствия дат и их числовых значений. Если такая есть, подскажите, пожалуйста, где её можно посмотреть или как её можно сделать.

Ещё раз огромное спасибо

Лион, вам нужно изменить настройку сводной таблицы, чтобы она считала не количество, а сумму. Почитайте, например, Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013.
Что касается соответствия дат и чисел, такой таблички нет. Но она и не нужна. Сначала внесите в ячейку дату, а потом выберите для ячейки формат «числовой».

Огромное спасибо

Треба написати формулу: в якій необхідно підрахувати ячейки-особи, котрі народилися певного місяця та певного року, якщо відомо повну їх дату народження (ДЕНЬ, МІСЯЦЬ, РІК). Наприклад: скільки чоловіків, мешканців сіл народилися в травні 1980 року. Крім того, є ще ряд умов які потрібно додавити.
Дякую за допомогу

Я инженер ПТО.
В перечне материалов мне нужно подсчитать общее количество кирпича керамического в разных строках перечня. Как это сделать?
СУММЕСЛИ(А1:А100;"кирпич керамический")?
Так? Помогите!

Николай, немного не так:

Здесь диапазон A1:A100 — в котором вы суммируете, а диапазон B1:B100 — в котором проверяете на совпадение с кирпичом.

Здравствуйте, я веду табеля и мне необходимо высчитать общие часы с условием,что 8\4=12.
У меня не получается, выхолит или 2 или выдает 8 апреля. Помогите составить правильно формулу.
Спасибо.

=(СЧЁТЕСЛИ(R[1]C[-38]:R[1]C[-8];"8/4")*12+ СЧЁТЕСЛИ(R[1]C[-38]:R[1]C[-8];"8/")*8+ СЧЁТЕСЛИ(R[1]C[-38]:R[1]C[-8];"/4")*4+ СУММ(R[1]C[-38]:R[1]C[-8])) это моя формула,чтоб было ясно о чем я говорю.

Елена, вы специально используете стиль формул R1C1? Как правило всё же используют стиль А1. Этот параметр можно установить, пройдя по меню Файл -> Параметры -> Формулы. Пришлите в личку Excel-файл, попробую помочь.

Добрый день! Помогите, пожалуйста, решить проблему. В таблице четыре столбца, которые повторяются ежемесячно. Необходимо в «итого» посчитать сумму всех ячеек, которые находятся в столбцах с одинаковым названием.

Елена, если я правильно понял ваш вопрос, могу предложить решение с помощью функции СУММЕСЛИ:

См. также Excel-файл

Добрый день. Подскажите как мне посчитать сумму чисел только тех напротив которых стоит плюс в соседней ячейке?

Уже сам нашел)
=СУММЕСЛИ(H:H;"";G:G)

Помогите как слить текстовые ячейки в одну но по условию из соседнего столбца? Ну или условное суммирование текстовых ячеек, без макросов.

Руслан, например, так:

не правильно сформулировал http://screenshot.ru/bf440ad07d84da9fcf776e5f095edc25 вот такое нужно получить

Здравствуйте. Мучаюсь уже битый час — не могу разобраться с вопросом: как присвоить ячейке определенную метку/категорию (не выделяя цветом)? Т.е. у меня есть определенные ячейки в разных местах, на разных листах; нужно эти ячейки «пометить» — присвоив категорию, например, «транспортные расходы», что бы потом получить общую сумму чисел в этих «помеченных» ячейках. Выручите пожалуйста!

Алексей, почитайте Excel. Имена диапазонов. Может быть, найдете решение вашей проблемы.

спасибо за наводку)

Калі ласка, ніжэй — варыянт макраса, які гэтае робіць.

--------------------------------------------------
Sub m_Add_Selection_to_Name()
'Add s elected range to existing range named "transport".
'When range with the name "transport" is not exist now -- it will be created.
    Const myRangeName = "transport"
    On Error Resume Next
    Dim myRange As Range, myNewRange As Range
    '
    Set myNewRange = Union(Range(myRangeName), Selection)
    If Err Then
    'When range with the name "transport" is not exist now -- it will be created.
        Set myNewRange = Selection
        MsgBox "New named Range will be created!"
    End If
    ActiveWorkbook.Names.Add Name:=myRangeName, RefersTo:=myNewRange
End Sub
—————————————————

Камэнтар:
1. Макрас далучае вылучаныя каморкі да вобласьці з назовам «transport», калі такая ўжо існуе, ці стварае гэтый назоў для вылучэньня.
Sic! Назоў «transport» (ці які патрэбны) задаецца ў тэксту макраса, праз канстанту myRangeName = «transport».
2. Сумму ўсік каморкаў, што належаць да вобласьці, лічыць формула: =СУММ(transport)
Удачы!

Здравствуйте! У меня схожий вопрос: Я инженер ПТО.
В перечне материалов мне нужно подсчитать общее количество кирпича керамического в разных строках перечня. Как это сделать?
СУММЕСЛИ(А1:А100;"кирпич керамический")?
Так? Помогите!» только мне нужно чтобы считались только по словам керамический, силикатный, и т.п., а слово кирпич при этом не затрагивались???

Добрый день.

Подскажите пожалуйста как возможно посчитать количество ячеек в строке, которые залиты условным форматированием?

Либо как подсчитать то же количество ячеек в строке, но при условии, что считаться будут только те ячейки, которые меньше определенного числа из соседней ячейки?

Спасибо.

Жанна, идея заметки, как раз в том и состоит, что Excel не может использовать цвет (или иной признак) условного форматирования для суммирования. Нужно использовать то же правило, что и в условном форматировании, и «вложить» это правило в функции СУММЕСЛИ или СУММЕСЛИМН (если правил несколько).

Добрый день!
Все предлагают случаи сложения каждой N-ой ячейки в столбце.
А как сложить N-ые ячейки в строке?
Напр.: D10+G10+J10+… т.д. Т.е. сумму ячеек через 2 (3,4,…).

Сергей, могу предложить формулу массива: =СУММПРОИЗВ(A3:L3;--(ОСТАТ(СТОЛБЕЦ(A3:L3); $A$1)=0)).

Здесь в ячейке А1 расположен делитель, а суммирование идет по диапазону А3:L3. См. также приложенный Excel-файл. Подробнее о формулах массива см. Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Подскажите, пожалуйста, как посчитать значения по нескольким условиям? Мне необходимо посчитать кол-во продаж указанных на второй странице (с 01.09 по 20.09) при совпадении нескольких условий (BBB и Яблоки)+(BBB и Зеленые яблоки)+(ВВВ и Красные яблоки).
=СУММЕСЛИМН(Лист2!C:H;Лист2!A:A;Лист1!A3;Лист2!B:B;{"Яблоки";"Зеленые яблоки";"Красные яблоки"})

Евгения, я не совсем понял ваши ссылки на разные листы, поэтому привожу решение, где все данные на одном листе:
=СУММЕСЛИМН(I2:I25;A2:A25;"ВВВ";B2:B25; "*Яблоки")

Поскольку Аргумент Диапазон_условия должен иметь то же количество строк и столбцов, что и аргумент Диапазон_суммирования (см. справку по функции), я добавил столбец Итого.
См. также Excel-файл.
Если я ответил не на тот вопрос, пришлите Excel-файл в личку. Подробно укажите, что требуется. Постараюсь помочь.

Спасибо большое за вашу помощь. Подскажите, как прописать несколько условий?
Не всегда в названии присутствует одинаковое слово «яблоки» (я для примера написала фрукты, чтоб было понятно).
Как прописать, чтоб формула считала три фрукта, те которые я укажу. В вашем примере: ВВВ сливы + ВВВ черешни + ВВВ зеленые яблоки.
Заранее благодарю за вашу помощь!

Евгения, не придумал ничего лучшего, чем просуммировать три фрагмента:
=СУММЕСЛИМН(I2:I25;A2:A25;»ВВВ»;B2:B25;B5)+ СУММЕСЛИМН(I2:I25;A2:A25;»ВВВ»;B2:B25;B10)+ СУММЕСЛИМН(I2:I25;A2:A25;»ВВВ»;B2:B25;B3)

См. Excel-файл

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

Добрый день, подскажите пожалуйста как можно суммировать диапазон цифр в колонке B при условии если в колонке E стоит 1 т.е. надо суммировать цифры попадающие от 1 до 1 по колонке E, и в клонке D посчитать количество записей.
A B C D E
01.08.2016 2 100 000,00 6 250 000,00 3 1
15.08.2016 1 550 000,00
31.08.2016 2 600 000,00
02.08.2016 0,00 7 800 000,00 5 1
06.08.2016 0,00
08.08.2016 2 600 000,00
16.08.2016 2 600 000,00
25.08.2016 2 600 000,00
30.08.2016 3 500 000,00 3 500 000,00 1 1
05.08.2016 2 600 000,00 7 800 000,00 3 1
12.08.2016 2 600 000,00
24.08.2016 2 600 000,00
05.08.2016 5 000 000,00 15 000 000,00 3 1
12.08.2016 5 000 000,00
20.08.2016 5 000 000,00

Павел, если я правильно понял задачу, то воспользуйтесь формулой =СУММЕСЛИ(E1:E15;1;B1:B15) для суммирования значений в столбце В при условии, что в столбе Е единица.
Функция =СЧЁТЕСЛИ(E1:E15;1) подсчитает число таких единиц.

Добрый день! Помогите пожалуйста. Нужно определить % повторных договоров у каждого клиента до 09.01.2015 и % повторных клиентов в общем количестве.
Дата Договор Клиент
05.01.2015 4841/2015 Катя
06.01.2015 4842/2015 Даша
06.01.2015 4843/2015 Даша
11.01.2015 4844/2015 Даша
09.01.2015 4847/2015 Маша
09.01.2015 4845/2015 Юля
10.01.2015 4848/2015 Виктор
12.01.2015 4849/2015 Костя

Мария, на мой взгляд, для решения ваших задач лучше всего подходят сводные таблицы. Правда, в вашем примере нет повторных договоров — все уникальные)) И что значит % повторных клиентов? Может быть, это все вхождения, начиная со второго? В этом случае доля повторных клиентов — 33%

См. также Excel-файл

Baguzin, помогите пожалуйста! Есть число, к нему нужно прибавить (или отнять) случайное число так чтобы сумма была, ну например, от 4 до 10. Может ли случайное число поменяться, если не выполняется данное условие???

Юлия, думаю, что такое невозможно…

Baguzin,спасибо! Не буду себе голову ломать….

Добрый день!
Подскажите, пожалуйста, как сделать так, чтобы если ячейка А заполнена, то дальнейшие вычисления производятся по ней, если пустая, то по соседней заполненной ячейке? Спасибо!

Например, так: =ЕСЛИ(A1″»;A1;B1). Подразумевается, что проверяется ячейка А1, и если она не пустая, то возвращается ее значение, а если пустая, то значение В1.

Доброго времени суток!
Подскажите пожалуйста как, сделать некоторые действия:
1) имеем ячейку со значением 2- тогда мы применяем расчет дважды по одной и той же формуле, но с подстановкой значения которое нашлось раньше, если число 3, тогда трижды ну и т.д.;
2) как сделать авто заполнение нескольких ячеек в зависимости от числа в другой ячейке, диапазон 5-6 чисел максимум;
3)как можно сделать (хотя бы не автоматическое) округление чисел, например имеем число 20,354 надо округлить до 25, а если 26,05 то округляем до 32.
Заранее очень благодарен.

Дмитрий, пришлите Excel-файл на s_bag@mail.ru. И подробнее опишите, что вам нужно. Пока не очень понятно…

Добрый день. Может поможете — есть массив допустим A4:D50 все ячейки постепенно заполняются разными цифрами, есть ли формула которая выделит ячейки по заданной сумме как только таковая будет совпадать.
Например нужно в сумме 100 — и как только в ячейках впишутся цифры сумма которых равна 100 , они бы выделились 🙂 Заранее спасибо.

Антон, если я правильно понял, то предлагаю решить с помощью условного форматирования. Выберите диапазон А4:D40 и задайте правило форматирования на основе формулы:
=(A4<>"")*(СУММ($A$4:$D$50)=100)=1

Чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
См. также Excel-файл

Добрый день, подскажите вот у меня таблица, разные года (начиная с 2006 по 2016) много данных на каждый год, каждый месяц. Как через функцию сегодня выставить формулу так чтоб показывали данные только за выбранный год)
Напимер стоит число 28.11.2016 — то все данные за 2016, если поставить число — 15.08.2010 — то все данные за 2010 год?

Анна, пришлите файл s_bag@mail.ru

Здравствуйте,очень нужна помощь.
У меня есть 7 таблиц (в одном файле эксель) которая включает: название, день недели и итог, вот как мне посчитать «ИТОГ» всех 7-ми таблиц и вместить всё в одну таблицу (в каждой таблице «НАЗВАНИЯ» не по порядку, как например в первой таблице, а все в разброс.
То есть, из всех этих таблиц (с итога) нужно впихнуть все в одну. (можна сказать в один итог). Просто названия в разброс, вот например, в 1 таблице: апельсины, бананы, киви, а в 2 таблице уже: бананы, мандарины, апельсины.

Дмитрий, пришлите файл s_bag@mail.ru

Спасибо з Ваш отклик. Нашел функцию «ВПР», которая мне помогла в решение этой задачи.

Подскажите, пожалуйста, как мне суммировать ячейки из диапазона по определенным условиям, при этом исключить ячейки со значением #Н/Д?
Я использую функцию СУММЕСЛИМН(), но никак не могу исключить ячейки с ошибкой. Если таких ячеек нет, то все ОК ,а вот если есть, то и результат — #Н/Д

Спасибо. Сам до этого искал, но так и не нашел.

Подскажите пожалуйста, у меня есть таблица, в первом столбце наименование деталей а,б,в, для каждой детали во втором столбце указаны операции 1,2,3,4, для каждой операции указан цех и норма времени. Как просуммировать норму для каждой детали внутри одного цеха? Сложность в том, что бывают такие ситуации, когда, допустим, первая операция выполняется в первом цехе, вторая — во втором, в третья — вновь в первом. Понимаю, что нужно как-то использовать СУММЕСЛИМН, но точный синтаксис не соображу.

Доброго времени суток. Подскажите в строке рабочего времени необходимо посчитать число рабочих часов исходя из 8 часов на каждый день, сложность в том, что есть ячейки где стоят цыфры больше 8.
Часы которые больше 8 (9-8=1час) занести в другой столбец. Спасибо.

Как подсветить наименьшее число в таблице в каждой строке?

Андрей,
1) выделите диапазон, с которым вы хотите работать
2) пройдите по меню Главная -> Условное форматирование -> Создать правило -> Использовать формулу для определения форматируемых ячеек
3) введите формулу, как указано на рисунке (с особым вниманием отнеситесь к комбинации абсолютных и относительных ссылок)
4) задайте желательное форматирование для выделяемых ячеек
5) нажмите Ok

См. также Excel-файл

Добрый день.
Необходимо узнать количество деталей с данным номером детали. Пример того как должно считать https://yadi.sk/i/gocQmzJe3FyMgm Т.е., например, есть детали с номером 433300000027 — надо посчитать сколько их в файле. Номеров деталей сотни, вручную много времени уходит по отдельности суммировать. Да и вероятность ошибки велика, когда глаза уже разбегаются…
https://yadi.sk/i/PfMLVaw73FyMgz

Иван, воспользуйтесь функцией =СЧЁТЕСЛИ().

Доброго времени суток!
Работаю с формулой СУММЕСЛИМН, подскажите, пожалуйста, есть ли возможность задать условие для диапазона ячеек с частичным содержанием текста?
Например нужно подсчитать металл толщиной 4 мм, но разных размеров:
-4(50*100)
-4(120х200)

Спасибо!

Юлия, попробуйте формулу =СУММПРОИЗВ(НЕ(ЕОШИБКА(НАЙТИ(B7;A2:A5))) *B2:B5), как на рисунке ниже. См. также Excel-файл.

Здравствуйте!
Помогите, пожалуйста, разобраться в вопросе. Заранее оговорюсь, что в моём экселе столбцы не буквенные а номерные — 1,2, 3,….26.. В столбце 10 прописан код классификатора. Таких кодов у меня 7 штук и они находятся в разных строках столбца 10. В столбце 26 у меня есть число (цена позиции). Мне необходимо подсчитать общую цену столбца 26 в соответствии с кодом классификатора. Как пример: стобец 10, в строке 2 стоит код 221, в столбце 26 указана цена 100 р. Такой же код находится в строке 10 и имеет цену в столбце 26 150 р. Рассчитываю, что формула будет брать значение из столбца 10 и суммировать данные из столбца 26… буду очень признателен за помощь в определении этой формулы.

Арсений,
> в моём экселе столбцы не буквенные а номерные
Если вы это сделали не специально, рекомендую вернуться к обычноому формату — А1. Для этого пройдите по меню Файл -> Параметры, перейдите на вкладку Формулы, и снимите галочку Стиль ссылок R1C1.

Что касается вашей проблемы, рекомендую функцию СУММЕСЛИ. Судя по описанию, она справится))

Будьте так любезны, напишите формулу в её натуренатуре. я так понимаю, её начало такое =СУММЕСЛИ(221;J2:J1000;(СУММ(Z2:Z100))

спасибо за рекомендацию. я взял 0,3 и таки добил формулу)
=СУММЕСЛИ(J2:J1000;"221"; Z2:Z1000)

Добрый вечер. Можете помочь, мне нужно создать такую формулу. Например, на первом листе (Фамилия и вид деятельности) деятельность разного вида. На последующих листах каждый вид деятельности отдельно вместе с фамилиями указанными на первом листе, такое типа сортировка. Буду признателен.

Здравствуйте! Помогите, пожалуйста, написать формулу (или хотя бы подсказать направление, в котором нужно искать решение). В ячейках A1:A10 находятся числовые значения. Ячейки B1:G10 могут быть как пустыми, так и содержать числа. Требуется найти сумму всех значений из тех ячеек диапазона A1:A10, для которых строка B:G из диапазона B1:G10 не содержит ни одного числового значения. Иными словами, если, к примеру, хотя бы одна из ячеек B3:G3 содержит числовое значение, то значение ячейки A3 не должно войти в итоговую сумму; а если, например, все ячейки B5:G5 пустые, то значение ячейки A5 входит в итоговую сумму. Конечно, можно было бы добавить формулу в столбец H, где ячейка H1 содержит формулу =Ч(СЧЁТ(B1:G1)>0) и т.д., а затем сумму найти по формуле =СУММПРОИЗВ(A1:A10;H1:H10). Но в этой задаче есть некоторые требования: 1) Искомая сумма должна быть выражена одной формулой; 2) Должна быть возможность увеличить количество строк путем вставки новой строки между 1 и 10 строкой; 3) Также не должно приводить к ошибкам, если добавить новый столбец между B и G. Заранее Вам благодарен!

Неким аналогом могла бы быть формула =СУММЕСЛИМН(A1:A10;B1:B10;"";C1:C10;"";D1:D10; "";E1:E10;"";F1:F10;"";G1:G10;"")
Но она не масштабируема в том смысле, что если вставить новый столбец между B и G, то формулу придется править руками, добавляя новый столбец в аргументы функции СУММЕСЛИМН

Николай, это интересная творческая задача)) Простого решения, скорее всего, нет. Можно написать функцию на VBA. Возможно, справятся формулы массива; см., например, Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel. Возможно будет полезно ввести имена диапазон, определенные пользователем. Одно — для диапазона А1:А10, второе — для В1:G10.

Добрый день! Подскажите, как мне правильно посчитать общее количество по всем типам элементов, входящих в каждый катер? Например вручную для самого первого Катера сумма будет 28. Но вот как создать формулу, которая бы подсчитывала сумму для каждого из Катеров, ведь типы элементов постоянно разнятся? Заранее спасибо!

Катер
болт — 4
гайка — 7
веревка — 2
доска — 15

Катер
болт — 2
доска — 3

Катер
веревка — 8
болт — 3
гайка — 7

Добрый день!
Как реализовать суммирование яйчеек «Количество портов» только во 2ом подъезде, потом в 5ом, и т.д. Вот только чтобы данную функцию можно было растянуть по всему файлу и она вывела бы сумму в определенную яйчейку «Итого». Спасибо.
Подъезд Количество портов Итого
2 12 17
2 5
5 2 8
5 6
1 24 25

Дмитрий, судя по описанию, с вашей задачей справится функция СУММЕСЛИ.

суммируешь в эксель по формуле например 10,12+6,37=16,49. а компьютер выдает 16,50, что делать

Ольга, это ошибка округления. Excel отражает значения с одной (заданной) точностью, а в памяти хранит числа с большим числом знаков после запятой. В арифметических операциях Excel использует числа максимально доступной точности. I6=H6+I5. На верхней части рисунка числа отображаются с двумя знаками после запятой, на нижней — с тремя:

Ольга, если у вас проблема с налоговй отчетностью, рекомендую Excel для бухгалтера: исправление ошибки округления.

Здравствуйте! Подскажите как из столца с фиксированными числами числа от 20 до 30 найти перенести в другой столбец автоматически? Заранее благодарю!

Здравствуйте!
Прошу помочь разобраться в следующем.
Итак, я считаю сумму затрат для раздела с кодом п.4 по следующей формуле =СУММЕСЛИ(D7:D106;"п.4";Z7:Z106), где в диапазоне D:D находится п.4, а в диапазоне Z:Z затраты.
далее, по формуле =СУММЕСЛИ(J7:J106;"221";Z7:Z106) я нахожу траты по разделу, имеющий код 221. У меня несколько разделов с разными кодами.
вопрос: как мне объединить п.4 и 221 и вычленить по ним затраты из общей таблицы расходов? Да, можно в ручную плюсиками прописать весь документ, но это очень длительный процесс.
Прошу помочь в моей проблеме…

Может быть, подойдет функция СУММЕСЛИМН?

я пытался использовать СУММЕСЛИМН, но не могу допетрить как расположить в ней п.4 и 221

то есть, при условии того, что в одном столбце находится п.4, в другом столбце находится 221, то берется сумма из третьего столбца (в моём случае это Z). И таким образом ведется подсчет по всем строкам документа.

=СУММЕСЛИМН(X7:X106;J7:J106;"221";D7:D106;"п.4") допетрил.


Прокомментировать