Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have values in a table like this

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

I would like to count by month how many 'Values' are negative (i.e. contain a '-' character). I have tried a few things without success. Assume that the values in 'Date' column are real dates so I can use functions like month(). The values in 'Values' column are text (i.e. prefaced with ') as I can't get Excel to cope with negative times correctly.

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

I was hoping this How can I sum values within a specific time and date range? would help, but I couldn't adapt it for my circumstance.

Appreciate some advice.

share|improve this question
    
For absolute clarity, I would use this formula for each month. The code example above is for January and the desired result is 2. –  aland Mar 5 '12 at 10:51

2 Answers 2

up vote 1 down vote accepted

You can't use wildcards when comparing with = so you need LEFT function in SUMPRODUCT formula like this

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

That will count January dates in any year

In Excel 2007 it might be easier with COUNTIFS, assuming you have the start date of the month to count in D2 (e.g. 1/1/2012) then use this formula for January 2012 only

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

share|improve this answer
    
The countif function looks really promising, but for some reason it's not counting on dates which are at the end of the month (eg. 31/01/2012). Which is really bizzare since we are using >= and <= –  aland Mar 5 '12 at 17:12
    
I can get it to work with =COUNTIFS(A:A,">="&D2,A:A,"<="&EOMONTH(D2,0)+1) but it seems really weird –  aland Mar 5 '12 at 17:15
1  
If column has has dates and times (times possibly not displayed but still present) then that won't count the last day - +1 will include those but then you should remove the = and use just < otherwise it could count dates beyond end of month, i.e. =COUNTIFS(A:A,">="&D2,A:A,"<"&EOMONTH(D2,0)+1,B:B,"-*") –  barry houdini Mar 5 '12 at 19:05
    
Yes I thought about this on the bus home. I do have time data in these fields. I guess EOM is returning 31/01 00:00 not 31/01 23:59, so I can use EOMONTH(D2,0)+TIME(23,59,59). Thanks, this is what I wanted. –  aland Mar 5 '12 at 22:20

To count negative values you can use =COUNTIF(A1,"-*")

To count for a specific month =IF(MONTH(B1)=1,COUNTIF(A1,"-*"),0) this will put a 1 for any negative value for month=1 (january). You can change the month = value for your month and you will get a column of 1s and 0s when you drag it down column C (helper column)

At the end of the helper column you can =sum(C:C) to count the number of negative values for a given month

of if you want to calculate the sum of the negative values =SUMIF(C:C,"=1",A:A)

Basically, what we're doing is using a helper column in column C. You can hide this column and do your =sumif or =if somewhere in Column D if you don't want the helper column to show.

edit

Changed the countif to count if it has a minus sign in it as the <0 won't work for non-numbers - should work

share|improve this answer
    
Thanks, sorry I wasn't clear why I was using if("-*",cellref) is that the data comes from an data import with values like '-1:48', '4:13' etc. I may have to transform this data to make my life easier. –  aland Mar 5 '12 at 12:36
    
Also, unless I'm missing something this would mean I need 12 'helper columns' for each month? Ok, I guess it works but I was hoping for some elegant solution. Realise this is not always possible, so appreciate your input. –  aland Mar 5 '12 at 12:38
    
@aland you can use the same helper column for each month, just when you get your count or sum, copy - special paste - values to another column with a label. –  Raystafarian Mar 5 '12 at 12:53
    
@aland Also I'm a bit confused on your first comment, would you clarify your question in regards to that, we may be able to manipulate this solution :) –  Raystafarian Mar 5 '12 at 12:55
    
Ok I've clarified my initial question. Sorry I guess my first edit for 'absolute' clarity was a bit ambitious :D –  aland Mar 5 '12 at 13:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.