Использование COUNTIFS для серии значений одновременно

Question

Работая на шаг выше, чем COUNTIFS, я ставлю задачу написать формулу без кода VBA. Исходные данные объединяются из 1000 строк:

• Столбец A: строки со значениями от 1 до 3
• Столбец B: строки со значениями от 1 до 250.

Для этого, скажем, мы смотрим на все ячейки значения "1" в столбце A, которые соответствуют значению "5" в столбце B. Чтобы найти все совпадения, мы использовали команду COUNTIFS.

1   1
2   5
1   5
1   7
1   10
3   45
2   12
1   2
2   1

=COUNTIFS(A1:A9;1;B1:B9;5)

Ответ здесь 1.

Далее, "5" в столбце B принадлежит группе, например, группе от 1 до 9. Как лучше всего посчитать все совпадения в этом примере, чтобы для всех "1" в столбце A нам нужно было найти все совпадения со значениями от 1 до 9 в столбце B?! В верхнем примере это приведет к "4". Очевидное решение - с помощью серии команд IF, но это неэффективно, и легко допустить ошибку, которая легко контролируется.

=COUNTIFS(A1:A9;1;B1:B9;"<="&9)

Работает только как верхний предел. Если я задаю третий диапазон критериев и условие как "> =" & 1, оно не работает - возвращает 0.

Я заметил, что три диапазона критериев и условия работают, только если я использую знак "=" в условии. Как только я использую

=COUNTIFS(A1:A9;1;B1:B9;"<="&9,B1:B9;">=1")

он возвращает 0. Моя цель состоит в том, чтобы в конечном итоге заменить число в условии командой VLOOKUP, поэтому окончательное уравнение должно быть чем-то вроде

=COUNTIFS(A1:A9;1;B1:B9;"<="&VLOOKUP(...),B1:B9;">=VLOOKUP(...)")

Но знаки "<" и ">" портят это. Все еще ищу решение.

дешевая сигарета

Answer 1

Хммм. Странно, а не как указано Microsoft. Я попытался воспроизвести проблему, но в Excel 2011 для Mac, а также в Excel 2010 для Windows она работает, как и ожидалось, с помощью следующей формулы: =COUNTIFS(A1:A9;E1;B1:B9;">="&E2;B1:B9;"<="&E3) (я использовал три ячейки для значений поиска, которые вы хотите использовать).

Answer 2

Спасибо! Это работает, я только что понял, что мои проблемы где-то еще. Это весь мой код:

=COUNTIFS(INDIRECT(CONCATENATE("baza!$";SUBSTITUTE(ADDRESS(1;MATCH("card_type_id";baza!$A$1:$AAA$1;0);4);"1";"");"$2:$";SUBSTITUTE(ADDRESS(1;MATCH("card_type_id";baza!$A$1:$AAA$1;0);4);"1";"");"$15000"));1;INDIRECT(CONCATENATE("baza!$";SUBSTITUTE(ADDRESS(1;MATCH(IF($C$4="CC_SI_klasifikacija";"building_classification_id";0);baza!$A$1:$AAA$1;0);4);"1";"");"$2:$";SUBSTITUTE(ADDRESS(1;MATCH(IF($C$4="CC_SI_klasifikacija";"building_classification_id";0);baza!$A$1:$AAA$1;0);4);"1";"");"$15000"));"<="&VLOOKUP(C5;K203:N210;4;FALSE))

Проблема в том, что мой второй диапазон критериев не распознается как числа в ячейках (рисунок ниже). Если вы посмотрите на формулу - это столбец N. Можно ли даже преобразовать текст в число в пределах одной формулы? например, NUMBERVALUE?

http://shrani.si/f/1C/Kx/mAYK9Wz/textnumber.jpg