Как в диапазоне посчитать количество ячеек соотвествующих двум критериям из двух выпадающих списков?

Question

[Neynon]

Как в гугл таблицах в диапазоне посчитать количество ячеек соотвествующих двум критериям из двух выпадающих списков?
пытался сделать через следующую формулу, то есть C4:C23 - диапазон в котором указаны первые выпадающие списки(Платформа), AJ5 - YouTube, D4:D23 - диапазон в котором указаны ячейки с другими выпадающими списками(Формат), AK5 - Короткий, но формула не считает
=СЧЁТЕСЛИМН(C4:C23; AJ5; D4:D23; AK5)

Answer 1

[yuliya_m]

Могу предложить два способа:

1. через arrayformula - требует повторять названия в каждой строке, но зато заполняется автоматически
   
2. через query - можно прописать названия напрямую в формулу, но требуется вставить формулу в каждую ячейку
   

Arrayformula

• Ячейки, которые предназначены для подсчёта ответа нужно заполнять отдельно. Визуально должна быть колонка условно "Ресурс" и колонка "Формат", а в строках последовательное заполнение Youtube / Широкий, Youtube / Короткий и т.д
  
• Рядом нужно создать колонку "Количество", которая будет считать число совпадений двух параметров
  
• В ячейку первой строки со значениями нужно вставить формулу:
  

  =ArrayFormula(ЕСЛИ(ЕПУСТО(D1:D); ""; СЧЁТЕСЛИМН(A1:A&B1:B; D1:D&E1:E)))

  
  
• Если всё заполнено верно, то все значения просчитаются автоматически
  

Пример

Query

• Для формулы не особо важно делается ли ссылка на значение ячейки или на конкретно заданное слово
  
  
• Если прописывается конкретное слово, то формула выглядит так:
  
  

  =QUERY(A:B; "select count(A) where A='Youtube' and B='Короткий' label count(A)''"; 0)

  
  
• Если указывается ячейка, тогда формула выглядит так:
  
  

  =QUERY(A2:B; "select count(A) where A='"&D2&"' and B='"&E2&"' label count(A)''"; 0)

  
  Важно не забывать указывать правильную ссылку на ячейку, иначе считаться будет не то значение.
  

Пример

Comments

[Neynon]

Добрый вечер, вы ЛЕГЕНДА, спасибо большое за совет и ваше время, сейчас буду тестить все варианты)

[Neynon]

Доброго времени суток!
Формулы к сожалению не работают((
Есть ли у вас возможность скинуть вашу тестовую таблицу?

[yuliya_m]

Neynon, есть, вот:
https://docs.google.com/spreadsheets/d/1zPNhdGdMmQ...

Могло не работать из-за неверного диапазона, поэтому обязательно нужно проверять букву столбца и номер ячейки. Надеюсь, что с тестовой таблицей всё получится!

[Neynon]

yuliya_m, всё получилось, подскажите, а если мне конечный результат необходимо посчитать в формуле, но некоторые ячейки ещё не заполнены, в целом вопрос стоит такой, как убрать N/A, формула еслиошибка перед query не работает))))

[yuliya_m]

Neynon, если речь только о том, чтобы посчитать суммы, то нужная формула:
=СУММ(ЕСЛИОШИБКА(A4:D4; 0)), где A4:D4 - диапазон одной строки с числами, которые нужно просуммировать

Если требуется ещё и декоративность, тогда:
- Выделить диапазон с ошибками, далее Формат -> Условное форматирование
- Пункт "Форматировать ячейки, если.." -> "Ваша формула"
- Формула =ЕОШИБКА(A4), где A4 - номер первой ячейки диапазона, где есть ошибки, которые хочется скрыть
- Далее настраиваем цвет текста и фона, после чего ошибки #N/A закрасятся и визуально исчезнут

В тестовой таблице добавила новые формулы и форматирование для примера.

[AlexDirektor]

yuliya_m, добрый вечер, вопрос по таблице, вот у вас сейчас там 3 столбца ABC, а вот допустим будет еще столбец D и там параметр например "загрузил/не загрузил" и табличка результата должна отражать количество ячеек по параметрам из столбца A и D (у вас сейчас А и В), подскажите пожалуйста какая формула, я пытался по логике поменять, но у меня постоянно ошибки, или валуе, или н/а или ерор.
вот такое дает n/a
=QUERY(A4:D; "select count(A) where A='"&I4&"' and D='"&J5&"' label count(A)''"; 0)
пример формулы на основе вашей таблички https://docs.google.com/spreadsheets/d/1zPNhdGdMmQ...
только я у себя добавил столбец D где стоят значения "загрузил/не загрузил" и в J5 у меня указано "загрузил"

https://docs.google.com/spreadsheets/d/1KIRaXayL77...
по сути проблема вот в такой таблице, мне надо посчитать ячейки в столбцу M по 2 признакам

[yuliya_m]

AlexDirektor, добрый

Проблемы несколько разного свойства:

1. В первом случае ошибка говорит о том, что значение не найдено. Сама формула верная, поэтому нужно проверить, что в столбце D и в ячейке J5 указано существующее значение - "Загрузил" или "Не загрузил", если брать пример
   Также нужно проверить синтаксис - это одинарная кавычка, двойная, амперсанд, ячейка и закрыли в обратном порядке - '"&R5&"'
   В тестовой табличке можно посмотреть на примере Youtube в диапазоне I4:K5
   
2. Во втором проблема чуть хитрее и связана с датами. Когда формула принимает на вход ссылку на ячейку, она ожидает текст или цифру, в то время как Google-таблица скрывает там формат даты.
   
   spoiler

   
   
   
   
   Попробовать можно два варианта:
   
   1. Выделить проблемную колонку и применить "Формат" -> "Числа" -> "Обычный текст". Но это исключает возможность разворачивания дат
      
      spoiler

      
      
      
      
      
   2. Задать в формуле ссылку на дату, но это уже ручной ввод.
      Ссылка задаётся как M= date'2025-10-01', где М - колонка с датами, а день указывается в формате гггг-мм-дд
      
      spoiler

      
      
      

[AlexDirektor]

yuliya_m, спасибо за ответ, но неужели нет инструмента обработки дат, в экселе вроде не было таких проблем...

тогда другой момент, может через доп таблицу с конвертацией в удобочитаемый вид просчитывать?
в этой таблице нужен именно такой формат, но мы же можем параллельно сделать таблицу/колонку с нужным нам форматом после автоконвертации?

[yuliya_m]

AlexDirektor, так в том и дело, что он есть)
И именно потому, что Google-таблица отличает дату от не даты, могут возникать сложности.

С конвертацией менее удобно, как вариант тогда - дату принудительно обернуть в текст:
М=date '"&ТЕКСТ(S5;"yyyy-MM-dd")&"'
То есть нужно формуле: сказать "это дата" -> показать, что далее будет ячейка -> заявить дату как текст -> указать с какой ячейкой сравнивать -> прописать интерпретацию даты -> закрыть синтаксис

В таком случае дата с датой сравниваться будет, даже если через формат заданы пользовательские дата и время - убран год, например.

[AlexDirektor]

yuliya_m, всё равно не понимаю, у меня в столбце M дата определенного формата, в ячейке S дата того же формата, почему он не коннектится на поиск?

и второе поиск с ячейками S8 b S9, где числовой поиск по столбцу P тоже почему то не работает

[yuliya_m]

AlexDirektor, сделайте тестовую табличку, посмотрю на досуге.
Скорее всего, дело в какой-то мелочи, потому что формулы сами по себе точно рабочие.