Excel Формулы. Как с помощью функции СЧЕТЕСЛИ посчитать количество выделенных цветом ячеек?

[vova]

Есть два столбца с датами.
В столбце "G" шрифт ячейки выделяется цветом с помощью условного форматирования по формуле =(G3-P3)>30 в диапазоне G3:G6, т.е. выделяет ячейку с разницей в датах более 30 дней.

Как с помощью функции СЧЕТЕСЛИ посчитать количество выделенных цветом ячеек в столбце G?

[Администратор]

С помощью формулы нельзя узнать, есть в ячейке цвет или нет.

Чтобы решить вашу задачу, есть два варианта:
1) можно сделать формулу, которая будет делать такие же расчёты, какие делает УФ;
2) можно сделать макрос. Макрос можно сделать в виде формулы, если вам нужно решение в виде формулы.

[vova]

Как сделать формулу, которая будет делать такие же расчёты, какие делает УФ?

[Администратор]

Можно такой формулой:
=СУММПРОИЗВ(--(G3:G6-F3:F6>30))

Расшифровка формулы

СУММПРОИЗВ. Эта функция предназначена для работы с несколькими массивами, а в вашем случае массив один, поэтому на первый взгляд нет смысла использовать эту функцию. Эта функция используется, чтобы не использовать фигурные скобки, которые вставляются сочетанием клавиш Ctrl+Shift+Enter.
Эта функция нужна, чтобы формула восприняла диапазоны G3:G6, F3:F6 как диапазоны.

--. Два минуса используются, чтобы перевести истину, ложь в числа: 1, 0. Чтобы затем сложить эти числа.

[vova]

Большое спасибо!

А если появляется второе дополнительное условие: подсчитать те строки, в которых в столбце Н пустая ячейка? Как будет выглядеть формула? Для данного примера формула должна вернуть "1".

И как посчитать сумму в столбце I, удовлетворяющую двум предыдущим условиям? Формула должна вернуть "20".

[Администратор]

Подсчёт количества:
=СУММПРОИЗВ((G3:G6-F3:F6>30)*(H3:H6=""))

Подсчёт суммы:
=СУММПРОИЗВ((G3:G6-F3:F6>30)*(H3:H6="")*I3:I6)


Пояснения к формулам

СУММПРОИЗВ. В этой функции массивы нужно разделять точкой с запятой, поэтому возникает вопрос: почему массивы не разделяются точкой с запятой, а используется символ умножения?
Это сделано, чтобы не использовать двойные минусы. Если массивы разделить точкой с запятой, то потребуется перед выражением ставить двойной минус, чтобы перевести истину, ложь в числа, чтобы подсчитать сумму. Если же используется знак умножения, то истина, ложь автоматически превращаются в числа.

[vova]

Отлично, Спасибо!