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.
        A          B
1      Date       Data
2    1/1/2014       1
3    1/2/2014       2
4    3/5/2014       3

I wanted to sum up rows for certain months. With month I can get the month and sumif lets me to sum rows that only meet the criteria. However since I don't know how to get the value of the cell and pass it to month I have to create a new column just for the month. And now the formula looks like:

=sumif(C:C,"=1",B:B)

I don't want to specify a column just for the month. Is there a way I can get the value of the cell and pass it to month for comparison (without using a new column)?

share|improve this question
    
I could not compare the outcome of month() function to a date... If you find something, do update here –  Prasanna Jul 28 at 21:23

2 Answers 2

up vote 2 down vote accepted
=SUMIFS(B:B,A:A,">="&DATE(year,month,1),A:A,"<"&DATE(year,month+1,1))
share|improve this answer
    
That's not quite what I'm trying to do. I want to compare the months to a number I define. In pseudo code it's something like this month(CURRENT_CELL) = 1 but I couldn't transform this into an excel formula. –  Derek 朕會功夫 Jul 29 at 18:17
    
Right, so if you want "month = 1", then you'd put in "DATE(2014,1,1)" and "DATE(2014,2,1)". Unless you mean the month irregardless of year? –  DarkMoon Jul 30 at 8:46
    
@DarMoon - I just reread your answer and I see what you are doing. It actually does what I'm trying to do (although I'm sure about December). Big thanks but is this the only way to do it? –  Derek 朕會功夫 Aug 1 at 2:46
    
The only way that I can think of, short of writing your own function. Do you know VBA? –  DarkMoon Aug 1 at 4:04
    
I don't know a lot about VBA but I do know a bit. I would definitely like to know a VBA solution. (In my previous comment there's a small typo. It should be I'm not sure about) –  Derek 朕會功夫 Aug 1 at 4:35
   Date Data                
01-06-2014  5           Day      1 
02-06-2014  6           Month    6 
03-06-2014  7           Year     2 014 
04-06-2014  8               
05-06-2014  9               
06-06-2014  4               
07-06-2014  2               
08-06-2014  3               
09-06-2014  1               
10-06-2014  5               
           50               

    =SUMIFS(E9:E18,$D$9:$D$18,">="&DATE(I11,I10,I9),$D$9:$D$18,"<"&DATE(I11,I10+1,I9))              
share|improve this answer

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.