Лічильники з логікою АБО для кількох критеріїв у Excel

Зазвичай ви можете використовувати функцію COUNTIFS для підрахунку клітин на основі однієї або кількох умов за допомогою логіки AND у Excel. Ви коли -небудь стикалися з ситуацією, коли вам потрібно порахувати більше одного значення з одного стовпця або діапазону клітинок? Це означає рахувати з кількома умовами та логікою АБО. У цьому випадку ви можете застосувати функції SUM та COUNTIFS разом або скористатися функцією SUMPRODUCT.

• Підрахувати клітинки з умовами OR в Excel
  • Підраховуйте клітинки з умовами OR за допомогою функцій SUM та COUNTIFS
  • Підраховуйте клітини з умовами АБО за допомогою функції SUMPRODUCT
• Підрахувати клітинки з декількома наборами умов АБО в Excel
  • Підраховуйте клітинки з двома наборами умов АБО за допомогою функцій SUM і COUNTIFS
  • Підраховуйте клітинки з декількома наборами умов АБО за допомогою функції SUMPRODUCT

Підрахувати клітинки з умовами OR в Excel

Наприклад, у мене є цілий ряд даних, як показано на скріншоті нижче, тепер я хочу підрахувати кількість продукту, який є "Олівець" або "Лінійка", тут я обговорю дві формули для вирішення цього завдання в Excel.

Підраховуйте клітинки з умовами OR за допомогою функцій SUM та COUNTIFS

У Excel для підрахунку з кількома умовами АБО можна використовувати функції SUM і COUNTIFS з константою масиву, загальний синтаксис:

• range: Діапазон даних містить критерії, з яких проводиться підрахунок клітинок;
• criterion1, criterion2, criterion3…: Умови, на основі яких потрібно підрахувати клітинки.

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

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

= SUM (COUNTIFS (B2: B13, {"Олівець", "Лінійка"}))

• {"Олівець", "Лінійка"}: По -перше, ви повинні упакувати всі умови в константу масиву, як це: {"Олівець", "Лінійка"}, розділити елементи комами.
• СЧІТКИ (В2: В13, {"Олівець", "Лінійка"}): Ця функція COUNTIFS отримає індивідуальні підрахунки для "Олівця" та "Лінійки", і ви отримаєте результат таким чином: {2,3}.
• SUM (COUNTIFS (B2: B13, {"Олівець", "Лінійка"})) = SUM ({2,3}): Нарешті, ця функція SUM підсумовує всі елементи в масиві та повертає результат: 5.

Поради: Ви також можете використовувати посилання на клітинки для критеріїв, застосуйте формулу масиву нижче, а потім натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати правильний результат:

Підраховуйте клітини з умовами АБО за допомогою функції SUMPRODUCT

Ось ще одна формула, створена функцією SUMPRODUCT, яка також може допомогти підрахувати клітинки з логікою АБО. Загальний синтаксис такий:

• range: Діапазон даних містить критерії, з яких проводиться підрахунок клітинок;
• criterion1, criterion2, criterion3…: Умови, на основі яких потрібно підрахувати клітинки.

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

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

= SUMPRODUCT (1*(B2: B13 = {"Олівець", "Лінійка"}))

• B2: B13 = {"Олівець", "Лінійка"}: Цей вираз порівнює кожен критерій "Олівець", "Лінійка" з коміркою діапазону B2: B13. Якщо критерій виконано, він повертає ІСТИНУ, інакше відображатиметься НЕВІРНО, ви отримаєте такий результат: {ІСТИНА, ЛОЖЬ; ФАЛЬШ, ФАЛЬШ; ФАЛЬШ, НЕВІРНО; ЛОЖЬ, ІСТИНА; НЕВІРНО, НЕВІРНО; ІСТИНА, НЕВІРНО ; FALSE, FALSE; FALSE, TRUE; FALSE, FALSE; FALSE, FALSE; FALSE, TRUE; FALSE, FALSE}.
• 1*(B2: B13 = {"Олівець", "Лінійка"}): Множення перетворює логічні значення - TRUE і FALSE на 1 та 0, тому воно отримає такий результат: {1,0; 0,0; 0,0; 0,1; 0,0; 1,0; 0,0 , 0,1; 0,0; 0,0; 0,1; 0,0; XNUMX}.
• SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))= SUMPRODUCT({1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}): Нарешті, функція SUMPRODUCT додає всі числа в масиві, щоб отримати результат: 5.

Підрахувати клітинки з декількома наборами умов АБО в Excel

Іноді вам може знадобитися підрахувати клітинки з двома або більше наборами умов АБО. У цьому випадку ви можете використовувати або SUM, і COUNTIFS з константою масиву, або SUMPRODUCT з функціями ISNUMBER MATCH.

Підраховуйте клітинки з двома наборами умов АБО за допомогою функцій SUM і COUNTIFS

Щоб мати справу лише з двома наборами критеріїв АБО, вам просто потрібно додати іншу константу масиву до формули COUNTIFS.

Наприклад, у мене є діапазон даних, як показано на скріншоті нижче, тепер я хочу порахувати людей, які замовили “Олівець” чи “Лінійку”, а сума - <100 або> 200.

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

примітки: У формулі слід використовувати крапку з комою для другої константи масиву, яка створює вертикальний масив.

Підраховуйте клітинки з декількома наборами умов АБО за допомогою функції SUMPRODUCT

Наведена вище формула працює лише для двох наборів критеріїв АБО. Якщо вам потрібно рахувати з більшою кількістю критеріїв, вам може допомогти складна формула SUMPRODUCT разом з функціями ISNUMBER MATCH.

Візьміть, наприклад, наведені нижче дані, щоб порахувати людей, які замовили "Олівець" або "Лінійка", а статус "Доставлено" або "Транзитно" та підписано "Бобом" або "Еко", вам слід застосувати складну формулу .

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

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

=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0)))

ISNUMBER (МАТЧ (В2: В13, {"Олівець", "Лінійка"}, 0)):

• МАТЧ (В2: В13, {"Олівець", "Лінійка"}, 0): Ця функція MATCH використовується для порівняння кожної комірки в діапазоні B2: B13 з відповідною константою масиву. Якщо збіг знайдено, він повертає відносне положення значення в масиві, інакше відображається значення помилки. Отже, ви отримаєте список масивів таким чином: {1;#N/A;#N/A; 2;#N/A; 1;#N/A; 2; 1;#N/A; 2;# Н/Д}.
• ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))= ISNUMBER({1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A}): Функція ISNUMBER перетворює числа на TRUE та значення помилок у FALSE так: {TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE}.

Наведена вище логіка також може бути застосована до другого та третього виразів ISNUMBER.

SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0))):

• Потім ці три масиви множаться разом у SUMPRODUCT, який автоматично перетворює значення TRUE та FALSE на 1s та 0s у рамках математичної операції, подібної до такої: SUMPRODUCT ({1; 0; 0; 1; 0; 1; 0; 1; 1; 0; 1; 0} *{1; 1; 0; 0; 1; 1; 0; 1; 1; 0; 1; 1} *{1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 1}) = ПІДВИСОК ({0; 1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 1}).
• Нарешті, функція SUMPRODUCT підсумовує всі числа в масиві, щоб отримати результат: 3.

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

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