СУММЕСЛИМН между двумя значениями в Excel и Google Таблицах

В этом руководстве показано, как суммировать данные для диапазона между двумя значениями с помощью функций СУММЕСЛИ и СУММЕСЛИМН.

Для больших наборов данных часто может потребоваться рассчитать общую сумму для заданного диапазона, т. е. суммировать данные на основе двух условий: «больше, чем A» и «меньше, чем B». Это легко сделать с помощью функции СУММЕСЛИМН как в Excel, так и в Google Таблицах.

СУММЕСЛИМН между двумя значениями в Excel

Формула работает в Excel 2007 – Excel 365

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

В том числе пороговые значения:

СУММЕСЛИМН(ценности, ценности,”>=мин_номер“, ценности“<=максимальное_число“)

Исключая пороговые числа:

СУММЕСЛИМН(ценности, ценности“> мин_номер“, ценности“<максимальное_число“)

Разница между двумя формулами только в логических операторах:

Чтобы включить пороговые значения в сумму, больше или равно (>=) и меньше или равно (<=) используются операторы.
Чтобы исключить пороговые числа, используйте лучше чем (>) и меньше, чем (<).

Например, чтобы суммировать числа в диапазоне C2:C10, которые больше 200 и меньше 300, используется следующая формула:

=СУММЕСЛИМН(C2:C10, C2:C10, “>200”, C2:C10, “<300")

Чтобы сделать формулу более гибкой, вы можете ввести наименьшее и наибольшее число в предопределенные ячейки (F2 и F3 в этом примере) и ссылаться на эти ячейки. В этом случае синтаксис критериев немного отличается: вы заключаете логический оператор в двойные кавычки и объединяете ссылку на ячейку с помощью амперсанда. Например:

=СУММЕСЛИМН(C2:C10, C2:C10, “>”&F2, C2:C10, “<"&F3)

СУММЕСЛИ между двумя значениями в Excel

Формула работает в Excel 2000 – Excel 365

Если у вас есть старая версия Excel, в которой функция СУММЕСЛИМН недоступна, вы можете эмулировать эту функциональность, используя ее единственный аналог — функцию СУММЕСЛИ.

Идея состоит в том, чтобы создать две отдельные функции СУММЕСЛИ:

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

Затем вы вычитаете последнее из первого и получаете желаемый результат.

СУММЕСЛИ(ценности“>мин_номер“) – СУММЕСЛИ(ценности“>=максимальное_число“)

Чтобы убедиться, что формула работает должным образом, давайте протестируем ее на том же наборе данных:

=СУММЕСЛИ(C2:C10, “>200”) – СУММЕСЛИ(C2:C10, “>=300”)

Или со ссылками на ячейки:

=СУММЕСЛИ(C2:C10, “>”&F2) – СУММЕСЛИ(C2:C10, “>=”&F3)

СУММЕСЛИМН между двумя числами с дополнительными критериями

Прелесть функции СУММЕСЛИМН в том, что она принимает несколько пар диапазон/критерий (до 127). Это означает, что в общую формулу СУММЕСЛИМН, описанную выше, вы можете включить столько различных критериев, сколько необходимо.

Например, для суммирования Виноград при продажах от 200 до 300 долларов формула принимает следующий вид:

=СУММЕСЛИМН(C2:C10, C2:C10, “>200”, C2:C10, “<300", B2:B10, "Виноград")

Со ссылками на ячейки это работает одинаково хорошо:

=СУММЕСЛИМН(C2:C10, C2:C10, “>”&F3, C2:C10, “<"&F4, B2:B10, F2)

SUMIF между в Google Sheets

Хорошая новость заключается в том, что формулы СУММЕСЛИМН, которые мы создали для Excel, работают и в Google Таблицах без единого изменения синтаксиса. Спасибо за последовательность, Microsoft и Google! 🙂

СУММЕСЛИМН между двумя числами

Чтобы получить сумму значений в столбце C, которые попадают в диапазон между числами в F2 и F3, используется следующая формула:

=СУММЕСЛИМН(C2:C10, C2:C10, “>”&F2, C2:C10, “<"&F3)

СУММЕСЛИМН между двумя значениями с другим условием

Чтобы сложить количество продаж для определенного товара в F2, которые находятся между числами в F3 и F4, формула выглядит следующим образом:

=СУММЕСЛИМН(C2:C10, C2:C10, “>”&F3, C2:C10, “<"&F4, B2:B10, F2)

Вот как можно суммировать данные между двумя значениями в Excel и Google Sheets. Удивительно просто, не так ли? Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!