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 would like to have the sum for dates in an specific month. The problem (differs from other questions) is that certain orders take months to be finished, and when these finally are, they are posted back to the month in which the order was received. So the date range can't be a specific array because the number of cells may increase over time.

So I need to be able to compare all of D for May for example (5) and sum E:

   A        B           C           D           E
1  Order#   Name        Product1    Date        Amount
2  24621    Customer1   product1    5/31/2013   $690.62
3  24621    Customer2   product1    5/31/2013   $297.07
4  24621    customer3   product1    5/22/2013   $274.20
5  24621    Customer4   product1    5/21/2013   $377.44
6  24521    Customer5   product1    5/20/2013   $3,200.00
7  24321    Customer5   product1    5/17/2013   $948.28

How can I achieve this?

share|improve this question
    
So, are you saying that you want to sum all May-dated amounts, for example, no matter where in the list of sales the transactions occurs? –  chuff Jun 6 '13 at 17:00
add comment

3 Answers

up vote 1 down vote accepted

You can use SUMIFS, e.g. in G2 put the 1st of the month like 1-May-2013 then use this formula in H2

=SUMIFS(E:E,D:D,">="&G2,D:D,"<"&EOMONTH(G2,0)+1)

You can list more dates in G3 down and then copy the formula down the column

share|improve this answer
    
Thanks man, works flawlessly. –  guisasso Jun 7 '13 at 13:45
add comment

Use a PivotTable (potentially of use for much more than your specific requirement) and Group by date:

SU604553 example

share|improve this answer
1  
Thank you for your answer, +1. –  guisasso Jun 7 '13 at 13:46
add comment

Easy to do in Excel:

  1. Add a helper column that calculates the Month of your data F2 =Month(D2). This should return 5.
  2. In your total cell, use the formula =SUMIF(F2:F7, 5,E2:E7). This will return the sum of all month values of 5 (May).
share|improve this answer
    
The problem is that d2 may change over time from one month to another. –  guisasso Jun 7 '13 at 13:46
add comment

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.