Сума, якщо клітинки містять певний текст в іншому стовпці

Цей підручник покаже вам, як підсумувати значення, якщо клітинки містять конкретний або частковий текст в іншому стовпці. Візьміть наведений нижче діапазон даних як приклад, щоб отримати загальну кількість товарів, що містять текст «Футболка», як функція SUMIF і SUMPRODUCT можуть впоратися з цим завданням у Excel.

• Підсумуйте значення, якщо клітинка містить конкретний або частковий текст із функцією SUMIF
• Сума значень, якщо клітинка містить конкретний або частковий текст із функцією SUMPRODUCT

Підсумуйте значення, якщо клітинка містить конкретний або частковий текст із функцією SUMIF

Щоб підсумувати значення, якщо клітинка містить певний текст в іншому стовпці, ви можете використовувати функцію SUMIF із символом підстановки (*), загальний синтаксис:

Загальна формула з жорстко закодованим текстом:

• range: діапазон даних, який потрібно оцінити за допомогою критеріїв;
• *text*: критерії, на основі яких потрібно підсумувати значення. Тут символ * використовується для пошуку будь-якої кількості символів, щоб знайти відповідність усім елементам, які містять певний текст, помістити текст між двома символами *. (Зверніть увагу, що ви повинні взяти текст і підстановку в подвійні лапки.)
• sum_range: діапазон комірок із відповідними числовими значеннями, які потрібно підсумувати.

Загальна формула з посиланням на клітинку:

• range: діапазон даних, який потрібно оцінити за допомогою критеріїв;
• "*"&cell&"*": критерії, на основі яких потрібно підсумувати значення;
  • *: символ підстановки, який знаходить будь-яку кількість символів.
  • осередок: клітинка містить конкретний текст, який потрібно шукати.
  • &: Цей оператор конкатенації (&) використовується для з’єднання посилання на клітинку із зірочками.
• sum_range: діапазон комірок із відповідними числовими значеннями, які потрібно підсумувати.

Дізнавшись основні принципи роботи функції, скористайтеся будь-якою з наведених нижче формул, а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ для отримання результату:

примітки: Ця функція SUMIF не чутлива до регістру.

Сума значень, якщо клітинка містить конкретний або частковий текст із функцією SUMPRODUCT

Функція SUMPRODUCT також може допомогти вам завершити цю роботу без використання символів підстановки. Тут ви повинні обернути функції ISNUMBER і SEARCH всередині функції SUMPRODUCT, загальний синтаксис:

• sum_range: діапазон комірок із відповідними числовими значеннями, які потрібно підсумувати;
• criteria: критерії, на основі яких потрібно підсумувати значення. Це може бути посилання на клітинку або певний визначений вами текст;
• range: діапазон даних, який потрібно оцінити за допомогою критеріїв;

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

Пояснення цієї формули:

=SUMPRODUCT($B$2:$B$12*(ISNUMBER(ПОШУК("Футболка",$A$2:$A$12))))

• ПОШУК("Футболка",$A$2:$A$12): Функція SEARCH повертає місцезнаходження конкретного тексту «T-Shirt» з діапазону даних A2:A12, отже, ви отримаєте такий масив: {5;#VALUE!;#VALUE!;7;#VALUE! ;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}.
• ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12))= ISNUMBER({5;#VALUE!;#VALUE!;7;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}): Ця функція ISNUMBER використовується для перевірки числових значень і повертає новий масив: {TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}.
• $B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))= {347;428;398;430;228;379;412;461;316;420;449}*{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}: Щоб помножити ці два масиви, математична операція встановлює значення TRUE і FALSE до 1s і 0s. Отже, множення двох масивів буде таким: {347;428;398;430;228;379;412;461;316;420;449}*{1;0;0;1;0;1; 0;0;0;0;1}={347;0;0;430;0;379;0;0;0;0;449}.
• SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))) =SUMPRODUCT({347;0;0;430;0;379;0;0;0;0;449}):Нарешті, функція SUMPRODUCT підсумовує всі значення в масиві, щоб отримати результат:1605.

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

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