Підсумуйте лише клітинки, що містять формули в Excel

Припустимо, що у вас є список значень в Excel, деякі з них створені за допомогою формул, тепер ви просто хочете підсумувати лише клітинки, які містять формули, як показано нижче. Як можна виконати це завдання в Excel?

Підсумуйте лише клітинки, що містять формули в Excel

У Excel можна створити просту формулу на основі функцій SUMPRODUCT і ISFORMULA, щоб підсумувати лише клітинки формули в діапазоні комірок, загальний синтаксис:

• range: діапазон даних, з якого потрібно підсумувати клітинки формули.

Будь ласка, введіть або скопіюйте формулу нижче у порожню клітинку, а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати розрахунок, див. скріншот:

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

=ПІДСУМНИЙ ПРОДУКТ(B2:B12*ISFORMULA(B2:B12)):

• ISFORMULA(B2:B12): Ця функція ISFORMULA використовується для перевірки, чи містить клітинка формулу. Якщо клітинка містить формулу, вона поверне TRUE, і FALSE, якщо ні. Отже, ви отримаєте такий масив: {FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}.
• B2:B12*ISFORMULA(B2:B12)= {50;150;35;45;200;80;95;45;50;30;108}*{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}: Математична операція встановлює значення TRUE і FALSE до 1s і 0s. Отже, множення двох масивів буде таким: {50;150;35;45;200;80;95;45;50;30;108}*{0;1;0;0;1;0; 0;1;0;0;1}={0;150;0;0;200;0;0;45;0;0;108}.
• SUMPRODUCT(B2:B12*ISFORMULA(B2:B12))= SUMPRODUCT({0;150;0;0;200;0;0;45;0;0;108}): Нарешті, функція SUMPRODUCT підсумовує всі значення в масиві, щоб отримати результат: 503.

Поради: тільки значення суми пропускають формули

Якщо вам потрібно підсумувати значення, пропускаючи клітинки формули, ви можете додати функцію НЕ у наведену вище формулу таким чином:

Використана відносна функція:

Більше статей: