формула, чтобы получить общее количество строк, содержащих значение в Excel

Question

У меня есть таблица с данными в этом формате:

User | Group       | ParentGroup | ID
joe  | US fed grp  |  fed        | 123
bill | dev         |  IT         | 234
jim  | UK fed grp  |  fed        | 345  

Мне нужна формула, которая будет искать столбцы Group и ParentGroup по всему листу и подсчитывать общее количество строк, содержащих «fed»

В приведенном выше примере формула будет возвращать 2 (а не 4, то есть количество раз, когда «кормят» появляется на всем листе). 2 - это количество строк, в которых отображается слово «feed».

я стараюсь

=COUNTIF(1:4, "*" & "fed" & "*")

но он возвращает 4 (я хочу 2)

Answer 1

Там нет необходимости для вспомогательной колонки:

=SUMPRODUCT(--ISNUMBER(SEARCH("fed",Group & ParentGroup)))

• Группа относится к B2:Bn (где n равно или больше, чем в последнем ряду)
• ParentGroup относится к C2:Cn
• Group и ParentGroup должны быть одинакового размера

И вы можете заменить ссылку на ячейку на "сытый", если хотите.

Answer 2

Ответ на одну ячейку:

=COUNTIF(B:C, "*fed*") - COUNTIFS(B:B, "*fed*", C:C, "*fed*")

Это будет подсчитывать количество ячеек, которые содержат fed , затем вычитать по одной для каждого ряда, который содержит две ячейки с fed . Это довольно прямо вперед, но не очень хорошо масштабируется (например , у вас есть 6 колонок , и вы хотите , чтобы подсчитать количество строк , которые содержат fed в любом из них)

---

Обновление: Масштабируемая версия

Это действительно беспокоило меня, поэтому я продолжал копать и придумал формулу массива:

=SUM(IF(ISNUMBER(SEARCH("fed", B1:B4)) +
        ISNUMBER(SEARCH("fed", C1:C4)),
        1, 0))

Важное примечание: это формула массива, поэтому вам нужно нажать CTRL + ALT + ENTER, а не просто вводить ENTER или щелкать за пределами ячейки. Вы можете сказать, правильно ли вы сделали, потому что формула будет отображаться { и } вокруг нее (и результат всегда будет 0, если вы этого не сделаете).

Теперь вы можете расширить это, чтобы взять любое количество столбцов, добавив еще один + ISNUMBER(SEARCH("fed", X1:X4)) к функции. Вы можете указать поиск во всем столбце, используя X:X , где X - буква столбца, но в зависимости от вашей системы, которая может начать работать медленно. У меня была заметная задержка при поиске по всему столбцу, но переключение на X1:X100 заставило его почувствовать себя мгновенно. Я считаю, что вам нужно будет сделать диапазон одинаковым для всех столбцов (например, у вас не может быть B:B и C1:C10), но я не проверял его.

Answer 3

Вот как я это решил.
Если в столбце B указано Group, столбец C - это ParentGroup, а в I2 - слово, которое вы ищете, в вашем случае сыт.
Создайте еще один столбец с именем FED, введите эту формулу и вытяните ее вниз. = ЕСЛИ (ИЛИ (EXACT (В2; $ I $ 2); EXACT (С2; $ I $ 2)); 1; 0)
При этом вы получите номер 1, даже если в каждом ряду мало "накормленных".
Затем суммируйте столбец FED.
Я загрузил решение.

Answer 4

Я считаю, что Давиденко прав, предлагая вам колонку помощника для этого.  Я предполагаю, что формула в, скажем, ячейке S2 , может быть что-то вроде

=OR(ISNUMBER(SEARCH("fed", B2)), ISNUMBER(SEARCH("fed", C2)))

Что будет иметь значение TRUE, если B2 или C2 содержат «fed», без учета регистра.  Замените SEARCH на FIND если вы хотите провести тест с учетом регистра. Если вы хотите, чтобы искомый текст приходил из ячейки, вы можете сделать это, например, с помощью

=OR(ISNUMBER(SEARCH($I$2, B2)), ISNUMBER(SEARCH($I$2, C2)))

Если вам нужно проверить столбцы A и D , добавьте их.

Быстрый и грязный ярлык будет

=ISNUMBER(SEARCH("fed", B2&C2))

но при этом будут учитываться строки, в которых значение в столбце B оканчивается на «f», а значение в столбце C начинается на «ed».  Чтобы исправить это, пойти с чем-то вроде

=ISNUMBER(SEARCH("fed", B2&"|"&C2))

Следующий,

• Заполните этот вспомогательный столбец (который я назвал выше S ), перетащив его вниз, и

• сделать что-то вроде

  =COUNTIF(S:S, TRUE)
  

В качестве альтернативы используйте один из распространенных приемов, чтобы преобразовать значения в столбце S в 1 и 0, а затем SUM (как предложил Давиденко).

Answer 5

=COUNTIFS(B:B,"fed",C:C,"fed")

при условии, что оба столбца имеют значение "сытый"