Как в EXCEL сложить числа в ячейках по определённому условию

Как в EXCEL сложить числа в ячейках по определённому условию
Многие работая с электронными таблицами EXCEL в определённый момент сталкиваются с вопросом «как в EXCEL сложить числа в ячейках по определённому условию». В один из солнечных дней я также столкнулся с данным вопросом, но на просторах интернета не нашёл ответ на данный вопрос, однако я настойчивый и решил изучить справку программы, которая меня и натолкнула на мысль, а далее методом проб и ошибок я составил формулу, и как оказалось формула элементарная.

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

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

Как в EXCEL сложить числа в ячейках по определённому условию

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

Сначала запишем готовую формулу, которую вставляем в ячейку D10, а потом начнём разбираться в деталях. Готовая формула выглядит следующим образом (только для нашей статьи):

=СУММЕСЛИ($G$5:$G$300;("Покупки в магазинах");$H$5:$H$300)

Цветом выделены различные условия, чтобы было наглядней. Разберём по порядку. В процессе описания смотрите на картинку выше, чтобы было понятней. Делая снимок специально были захвачены буквы столбцов и цифры строк. Итак, приступаем.

  • СУММЕСЛИ – этим условием мы говорим, что в ячейку надо записывать сумму значений определённых ячеек, если они соответствуют определённым условиям;
  • $G$5:$G$300 – здесь мы указываем EXCEL, в каком столбце нам надо искать условие для выборки. В нашем случае поиск происходит в столбце G начиная со строки 5 и заканчивая строкой 300;
  • Покупки в магазинах») – здесь мы указываем искомое условие и по этому условию будут суммироваться значения ячеек, которые мы указываем далее…;
  • $H$5:$H$300 – здесь мы указываем столбец, из которого будут браться числа для суммирования. В нашем случае значения берутся в столбце H начиная со строки 5 и заканчивая строкой 300.

Подводя итог можно сказать, что EXCEL суммирует только те значения из диапазона H5:H300, для которых соответствующие значения из диапазона G5:G300 равны «Покупки в магазинах» и записывает результат в ячейку D10.

Соответствующим образом можно в EXCEL сложить числа в ячейках по любому условию.

Знак $ в формуле используется для того, чтобы при копировании формулы с ячейки D10 в другие ячейки не происходило смещение. Рассмотрим пример формулы без знака $. К примеру, в ячейке D10 у нас вписана формула:

=СУММЕСЛИ(G5:G300;("Покупки в магазинах");H5:H300)

Далее мы хотим выводить сумму обедов в ячейке D11. Чтобы нам не переписывать формулу, нам можно копировать ячейку D10 и вставить в ячейку D11. Благодаря этому формула будет вставлена в D11, но тут мы можем заметить, что формула изменила значения заменив 5 на 6 и 300 на 301:

=СУММЕСЛИ(G6:G301;("Покупки в магазинах");H6:H301)

Произошло смещение. Если мы скопируем формулу в D12, то увидим уже смещение на 2 и так далее. Чтобы этого избежать мы формулу пишем со знаком $. Такие особенности EXCEL.

Таблица «Учёт ежемесячных расходов» — скачать

17 комментариев

  1. Спасибо!

  2. Владимир

    Опять я, думалось комментарий не будет отправлен по причине обычных заморочек типа зайти с ВК и тому подобное…
    Хорошая статья, искал давеча как с кабельного журнала метраж извлечь, считая себя знатоком экселя и… видимо ошибался :(.
    А тут вот-раз и готово! У нас девочки по полдня с калькулятором сидят. Спасибо еще раз!

  3. спасибо

  4. спасибо.Четко,емко,понятно!!!! СПАСИБО!!!!!!

  5. Отличная статья!

  6. а возможно ли тоже самое провернуть, но только чтобы эти «покупки в магазинах» складывались из нескольких таблиц с результатом в одну ячейку???

    • Скорее всего можно, но тут надо думать как. Сразу решения в голове нет.

      • Вот поддерживаю. Как раз нужно такое сделать, но пока не знаю, как.

  7. Добрый день!
    Пожалуйста, подскажите, что нужно применить (формулу или макрос) для подсчета в таблице буквенные обозначения, например, Р, Ф, Д. При этом Р=3, Ф=1, Д=4.
    Т.е. в таблице будут стоять буквы (в одной строке и столбце могут располагаться все буквы), а подсчитываться должно значение

  8. Доступно и понятно,благодарю!)

  9. Как раз тоже считаю бюджет и задалась вопросом как категории посчитать быстрее. Ваша статья пришлась кстати. Благодарю!

  10. Владимир

    Добрый день! Копируя историю операций в Сбербанк онлайн, к числам автоматом добавляются буквы «руб.», бьюсь не первый месяц не могу избавится от руб., так как с ними автосумма в эксель всегда равна 0, то есть формула не работает. Может кто сталкивался с такой проблемой и знает решение?

    • Дартаньян

      Заменой по всему документу можно. Заменить «руб.» на ничто.

      • Владимир

        Спасибо за ответ, но именно в этом и состоял вопрос-как это сделать. Я не очень хорошо знаю нюансы экселя и не смог найти решение этого вопроса.

        • Захаров Виктор

          В открытом документе нажмите CTRL + H.
          Откроется форма замены текста в документе.
          В верхнем поле введите «руб.» (если надо, то с начальным пробелом).
          В нижнем поле ничего не вводите.
          Нажмите на кнопку «Заменить всё».
          Программа заменит «руб.» на «ничто».

  11. Спасибо! То, что искала)

  12. Александр

    ДД, Нужна помощь, есть такая таблица, где есть регион, адрес, месяц:
    МО Краснопрудная, 20 Декабрь
    МО Краснопрудная, 3 Июнь
    МО Краснопрудная, 3 Июнь
    Москва Краснопрудная, 20 Декабрь
    МО Краснопрудная, 20 Июнь
    задача, сделать формулу, чтоб выбрав критерий «МО», а затем месяц «Июнь», одинаковые адреса превращались в значение 1 и к ним добавлялись уникальные адреса. Таким образом получим, что в МО в Июне было 2 уникальных адреса.

Добавить комментарий

Ваш адрес email не будет опубликован.