Подсчет отрицательных значений в диапазоне дат (месяц)

Question

У меня есть значения в таблице, как это

Date    Values
01/01    5:35
02/01   -7:08
02/01    3:45
15/01   -4:05
02/02    3:25
13/02    4:25
19/02    6:20

Я хотел бы посчитать по месяцам, сколько «значений» являются отрицательными (то есть содержат символ «-»). Я попробовал несколько вещей без успеха. Предположим, что значения в столбце «Дата» являются действительными датами, поэтому я могу использовать такие функции, как month (). Значения в столбце «Значения» являются текстовыми (то есть с предваряющим знаком «), так как я не могу заставить Excel справиться с отрицательными временами правильно.

=SUMPRODUCT(--(A2:A9999<>""),--(MONTH(A2:A9999)=1),IF("-*",B2:B9999))

Я надеялся, что это Как суммировать значения за определенный промежуток времени и даты? помог бы, но я не мог приспособить это к моим обстоятельствам.

Ценю несколько советов.

Answer 1

Для подсчета отрицательных значений вы можете использовать =COUNTIF(A1,"-*")

Для подсчета за конкретный месяц =IF(MONTH(B1)=1,COUNTIF(A1,"-*"),0) это будет 1 для любого отрицательного значения для month=1 (январь). Вы можете изменить значение month = value для вашего месяца, и вы получите столбец с 1 и 0 при перетаскивании его вниз на column C (вспомогательный столбец).

В конце вспомогательного столбца вы можете =sum(C:C) подсчитать количество отрицательных значений за данный месяц.

из если вы хотите вычислить сумму отрицательных значений =SUMIF(C:C,"=1",A:A)

По сути, мы используем вспомогательный столбец в column C Вы можете скрыть этот столбец и сделать свой =sumif или =if где-то в Column D если вы не хотите, чтобы отображался вспомогательный столбец .

редактировать

Изменен countif для подсчета, если в нем есть знак минус, так как <0 не будет работать для не чисел - должен работать

Answer 2

Вы не можете использовать подстановочные знаки при сравнении с =, поэтому вам нужна функция LEFT в формуле SUMPRODUCT, как это

=SUMPRODUCT(--(A2:A9999<>""),--(MONTH(A2:A9999)=1),--(LEFT(B2:B9999)="-"))

Это будет считать даты января в любом году

В Excel 2007 это может быть проще с COUNTIFS, если у вас есть дата начала месяца для подсчета в D2 (например, 01.01.2012), тогда используйте эту формулу только для января 2012

=COUNTIFS(A:A,">="&D2,A:A,"<="&EOMONTH(D2,0),B:B,"-*")