Excel: Countifs "Не равно"

У меня проблемы с написанием Countifs для выполнения того, что я хочу. Каждый месяц я запускаю отчет о претензиях с тоннами данных, организованных в столбцах, и экспортирую их в Excel каждый месяц. На отдельной вкладке есть формулы, которые ссылаются на вкладку, на которую эти данные копируются.

Формулы используются для подсчета и упорядочивания претензий в соответствии с определенными критериями. Одним из столбцов (G: G) является «Состояние юрисдикции».

Государство юрисдикции по каждому требованию важно, потому что определенные группы «особых государств» получают уникальную ставку, а «неспециальные государства» получают фиксированную ставку.

Я понял, как считать «особые состояния» без проблем. моя проблема заключается в подсчете «неспециальных состояний». Мне нужно написать счеты, которые ссылаются на диапазон, например 010: U10, и считать все в столбце G EXCEPT, что хранится в диапазоне O10: U10. Мне нужно 010: U10, чтобы иметь пустые ячейки для расширения. Сообщите мне, нужна ли какая-либо дополнительная информация.

Это можно сделать с помощью формул Array в одной ячейке, но, как вам кажется, вам не нравится Excel, я бы рекомендовал использовать некоторые вспомогательные столбцы.

Поэтому вместо того, чтобы просто читать непосредственно из строки 10 в O-> U и пытаться найти все из них в вашей формуле COUNTIFS, добавьте новый столбец строк в свои данные. В этой новой колонке указывается только, является ли юрисдикция в столбце G специальным состоянием. Так, например, если предположить, что $ O $ 10: $ U $ 10 имеет список всех особых состояний [я не понимаю, на каком листе все здесь, поэтому вам может понадобиться настроить). В H2 и скопируйте все записи данных, поставьте следующее [Это предполагает, что ваши данные начинаются с G2]:

=IF(ISERROR(MATCH(G2,$O$10:$U$10,0)),"Non-Special State","Special State") 

Это говорит: попробуйте MATCH имя юрисдикции из G2 из списка всех специальных состояний в строке 10. Если нет совпадения, это означает, что это не особое состояние, а MATCH вернет ошибку. Затем ISERROR вернет TRUE, что сделает заявление IF выдаст результат «Неспециальное государство». Однако, если MATCH найдет результат, он вернет «Особое состояние».

Затем в формуле вашего счета добавьте следующие критерии:

 =COUNTIFS(SUM COLUMN, OTHER CRITERIA COLUMN, OTHER CRITERIA, H:H, "Non-Special State") 

Способ сделать то, что вы просите, хотя я бы советовал против этого, было бы чем-то вроде

 =COUNT(A2:A21)-COUNTIF(A2:A21,C1)-COUNTIF(A2:A21,C2) 

Итак, для каждой ячейки, которую вы хотите включить, долго и утомительно.

Но, как вы говорите, вы можете считать особым, просто сделайте «итого» минус «специальная сумма»,

Любая формула, которая в одиночку выполняет это, будет использовать циклический расчет (с или без Ctrl + Shift + Enter или CSE), поэтому вы захотите сократить диапазон ячеек в столбце G до абсолютно необходимого. Полные ссылки на колонки приведут к вычислению множества пустых ячеек, которые не обязательно должны быть.

Как стандартная формула non-CSE с полной ссылкой на столбец с G: G,

 =SUM(INDEX(SIGN(LEN(G:G))*ISERROR(MATCH(G:G, O10:U10, 0)), , )) 

Имея ссылки на динамический диапазон,

 =SUM(INDEX(SIGN(LEN(G2:INDEX(G:G, MATCH("zzz", G:G ))))*ISERROR(MATCH(G2:INDEX(G:G, MATCH("zzz", G:G)), O10:U10, 0)), , )) 

sum_countif34

Формула предполагает, что столбец G содержит текст, а не числа.

  • Получить VLOOKUP, чтобы выбрать самую последнюю дату из выделения
  • найти самую новую запись для каждого элемента из списка
  • Диапазон дат по номеру
  • Excel - найти наименьшую разницу между значением и списком и связанным индексом
  • Использование совпадения vlookup или index внутри функции sumproduct
  • Функция Excel вставить несколько букв в каждую пустую ячейку во всей колонке
  • Возвращающая ячейка как переменная из функции в Excel
  • Вычисление мажоритарной стоимости в Excel
  • Условное AGGREGATE / Медиана в Excel 2010
  • Оценить текст, содержащий именованный диапазон
  • Excel - Умножить текст и отсортировать в одном столбце
  • Interesting Posts

    Суммы Sumif, основанные на месяцах для нескольких месяцев

    Как использовать сложные функции в Excel 2003 VBA?

    получить значение из списка и записать его в ячейку excel

    Файл xlsm заблокирован для редактирования другим пользователем – ошибка

    Добавление значений ячеек на основе строки в следующей ячейке

    Частичная ячейка включает в таблицу Excel

    Повторение имен динамических рабочих листов

    Как получить номер строки, который удовлетворяет условию

    Чтение файла Excel с помощью POI EventModel

    Разделение строк на основе нескольких правил и значений

    Экспорт отчета с несколькими листами с JasperServer в Excel

    Дата Время Столбец базы данных не принимает значения из листа excel с помощью SQLBULKCOPY

    Эффективный способ (используя apache POI) для чтения XLSX, заменить первую строку и записать результат?

    Нужно сделать набор графиков в excel с помощью макроса vba

    создать диаграмму с данными из нескольких листов с помощью VBA

    Давайте будем гением компьютера.