Как создать формулу Excel SUMIF, которая может сравнивать разные форматы дат

Question

Для моих исходных данных у меня есть столбец дат в формате dd/mm/dddd и столбец значений рядом с ним.

например

03/02/2014  5 
17/02/2014  4 
03/03/2014  5 
17/03/2014  4

На итоговом листе у меня есть ряд ячеек с датами в формате ммм-гг.

например

Apr-11        May-11        Jun-11

В ячейке под каждой из этих дат мне нужно ввести сумму всех значений источников, где месяц соответствует заголовку.

Я знаю, что могу сравнить два формата даты, используя:

MONTH(A1)=MONTH(D4)

И если даты совпадают, я мог бы использовать SUMIF следующим образом:

=SUMIF(A1:A53, =D4, B1:B53)

И я считаю, что в Excel 2007+ есть SUMIFS, которые могут мне помочь, но, к сожалению, я обязан сделать это в Excel 2003.

Но я не могу понять, как заставить работать сравнение месяцев внутри функции SUMIF.

Если это возможно?

Answer 1

Я не могу найти способ сделать это за несколько шагов, так что ...

Предположения
Я предполагаю, что на последнем листе, где мы видим только месяц-год, дата по-прежнему сохраняется в виде даты, а затем форматируется как месяц (поэтому под ней на самом деле написано 01.01.2014, а не 01- 2014.

Я также предполагаю, что ваша главная дата в столбце А.

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

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

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

Самое простое решение
Я никогда не был фанатом решений, которые полагаются на "вспомогательные столбцы", но пока, по крайней мере, это необходимо.

Создайте столбец рядом с вашим основным столбцом даты (это ваш вспомогательный столбец, и вы можете скрыть его или скрыть его группой для обычного использования) и в первую ячейку строки добавьте следующую формулу:

=EOMONTH(A2,0)

EOMONTH (Конец месяца) вернет последний день месяца для целевой даты. И число после него позволяет вам найти последний день других месяцев (введите -1, и он найдет последний день предыдущего месяца)

Скопируйте эту формулу и перейдите туда, куда вы хотите, чтобы SUMIF пошел. В этой ячейке введите это:

=SUMIF($B2:$B10,EOMONTH($D$1,0),$C2:$C10)

Где $B2:$B10 - диапазон вспомогательного столбца, $D$1 - значение месяца, а $C2:$C10 - диапазон суммируемых значений.

Теперь повторите по мере необходимости.

РЕДАКТИРОВАТЬ
Если вы не можете использовать EOMONTH из-за невозможности использовать требуемые пакеты дополнений, вы можете вместо этого использовать эту формулу.

=MONTH($A2)&YEAR($A2)

=SUMIF($B2:$B10,MONTH($D$1)&YEAR($D$1),$C2:$C10)