SUMIFS, использующие "больше или равно" с текстовыми значениями

Question

Используя следующий упрощенный пример, я хочу вычислить сумму, где Category = "Graduation" и Date> = 1986-05-01.

Category    Date      Count
Graduation  1985-06-15  1
Graduation  1985-09-12  2
Graduation  1986-05-21  3
Graduation  1986-06-06  4
Graduation  1986-07-03  7
Transfer    1986-08-14  3
Graduation  1986-08-20  1

Даты все текстовые, т.е. НЕ даты Excel. Будучи в формате гггг-мм-дд, даты следуют в правильном порядке.

Вот несколько формул, которые я использовал, и их результат, который приводит к моему настоящему вопросу в конце:

=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,"='1986-05-21'")     0   Incorrect
=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,"=1986-05-21")       3   Correct
=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,"1986-05-21")        3   Correct

Так что для равенства вы не ставите кавычки вокруг значения. Все в порядке.

=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,">1986-05-21")       0   Incorrect
=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,">'1986-05-21'")     12  Correct

Так больше, вы должны поставить кавычки вокруг значения.

=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,">='1986-05-21'")    12  Incorrect: should be 15

Похоже,> = рассматривается как если бы это было>.

=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,"<'1986-05-21'")     6   Incorrect: should be 3
=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,"<='1986-05-21'")        6   Correct

Похоже, что <обрабатывается так, как если бы это было <= (то есть наоборот> =).

Итак, можете ли вы помочь мне настроить синтаксис так, чтобы SUMIFS получалось greater than или less than правильно с текстовыми значениями?

Answer 1

Семейство функций COUNTIF(S)/SUMIF(S), как правило, пытается интерпретировать ваши данные, где это возможно, как числовые, хотя здесь, очевидно, существует некоторая путаница, поскольку не представляется, что она может последовательно интерпретировать как значения вашего диапазона, так и ваш критерий как того же формата.

В этом случае:

= СУММЕСЛИМН (С2: С8, А2: А8, "Выпускной", В2: В8, "> 1986-05-21")

Excel распознает ваши записи в B2:B8, хотя они отформатированы как текст, как потенциальные (то есть числовые) значения.

Однако, в то же время, он (бесполезно) интерпретирует ваш критерий - «1986-05-21» - как текстовое значение и т.д., Так как Excel не учитывает числовые значения (а именно это и есть даты в Excel - 21/05/1986 равен 31553), чтобы быть "больше", чем любое текстовое значение (введите, например, = 1000000> "1" в ячейке где-то - ответ ЛОЖЬ), ответ равен нулю.

Когда вы добавляете апострофы:

= СУММЕСЛИМН (С2: С8, А2: А8, "Выпускной", В2: В8, "> '1986-05-21'")

Excel интерпретирует и ваш критерий, и записи в B2:B8 как текст, так же как и прямое сравнение текста.

Причина, по которой вы получаете 12, а не 15, для:

= СУММЕСЛИМН (С2: С8, А2: А8, "Выпускной", В2: В8, "> = '1986-05-21'")

является то, что запись в B4 1986-05-21 (без апострофа: можно появиться в строке формул, хотя это технически не является частью строки, просто способ Excel указать, что значение ячейки является текстом) и критерий «1986-05-21» (с "подлинными" апострофами). И вы можете легко проверить в клетке, что:

= "1986-05-21"> = " '1986-05-21'"

Excel рассматривается как FALSE (я полагаю, что Excel интерпретирует главный апостроф в последнем как означающий, что он "больше", чем первый - я не знаю тонкостей этой интерпретации: странно, = "/a"> "a "возвращает FALSE, тогда как =" 'a ">" a "возвращает TRUE).

Все это, возможно, является хорошей причиной для перехода на SUMPRODUCT, который, похоже, не страдает такими своеобразными неясностями:

= СУММПРОИЗВ ((A2:A8 = "Выпускной")* (В2: В8> = "1986-05-21")* С2: С8)

С уважением