Чтобы найти сумму чисел на основе критериев, мы можем использовать комбинацию функций СУММ, ПОИСКПОЗ, СЧЁТЕСЛИ и СМЕЩЕНИЕ для получения результата.

СУММ: складывает все числа в диапазоне ячеек

Синтаксис: = СУММ (число1; число2; …​)

Может быть максимум 255 аргументов. См. Приведенный ниже снимок экрана:

img1

Функция ПОИСКПОЗ ищет указанный элемент в выбранном диапазоне ячеек, а затем возвращает относительное положение этого элемента в диапазоне.

Синтаксис = ПОИСКПОЗ (искомое_значение, поиск_массив, совпадение_тип)

СЧЁТЕСЛИ: подсчитывает количество ячеек в диапазоне, удовлетворяющем условию.

Синтаксис: = СЧЁТЕСЛИ (диапазон, критерий)

СМЕЩЕНИЕ: возвращает ссылку на диапазон, который представляет собой заданное количество строк и столбцов из заданной ссылки.

Синтаксис: = OFFSET (ссылка, строки, столбцы, высота, ширина)

ссылка: это ссылка на ячейку или диапазон ячеек, от которых будет применяться смещение.

rows: количество строк вверх или вниз, которые вы хотите применить в качестве смещения к диапазону.

cols: количество столбцов слева или справа, которые вы хотите применить в качестве смещения к диапазону.

высота: это необязательно. Это количество строк, которое вы хотите, чтобы возвращаемая ссылка.

ширина: это необязательно. Это количество столбцов, которое вы хотите, чтобы возвращаемая ссылка.

[.underline] # Давайте возьмем пример: # Столбец A содержит список чисел в порядке возрастания. Нам нужна формула, которая суммирует все числа в столбце A больше 2, что является нашим критерием в ячейке C2.

img2

  • Формула в ячейке D2 будет

* = СУММ (СМЕЩ (A2; ПОИСКПОЗ (C2; A2: A10,0), 0, СЧЁТЕСЛИ (A2: A10, «>» & C2)))

img3

  • В формуле взята сумма чисел больше 2.

  • Если мы изменим критерий в ячейке C2 с 2 на 5, результат будет обновлен автоматически. На этот раз формула вычислит сумму чисел больше 5.

img4