Another Excel query - comparing dates?

[Davi]

Getting more complicated now (to much for me!),

I need a function that will take make a calculation based between 2 sets of dates, using the current month to make the calculation. sort of.

so.... column has dates in it.
next column has figures in it.

I need to add together all figures where the dates are between the 11th of the previous month, and the 10th of the current month, so for example

If you have the following figures

1: 08/07/2006 110
2: 09/07/2006 120
3: 12/07/2006 150
4: 03/08/2006 180
5: 09/08/2006 170
6: 16/08/2006 220

Then it will add up the figures in rows 3, 4 and 5, giving 500

How?! I can get it to tell me the current date, but that's about it

[Jinx]

Now that is getting a little complicated - I have a solution on a excel sheet using basic functions (hopefully obvious to work out) email me through my profile and I'll send it to you (its not an elegant solution but does work).

[Davi]

Many Thanks Jinx, email sent, though I have had some troubles with the profile emails in the past - if you dont receive it mine is dongledell AT hotmail.com.

ETA elegance is never a concern with me, I cant even get to the working bit

Edited by Davi on Thursday 31st August 13:33

[Miles_stylus]

Not to bad - nto elegant but it works:

Assuming your dates are in column A (starting A1) and the values in B:B

1. in E1 put =month(today())
2. in G1 put "min", in G2 put "max" and G4 put "total"
3. in H1 put =DATE(2006,E1-1,11)
4. in H2 put =DATE(2006,E1,10)
5. in C1 put =IF(AND(A1>=$H$1,A1<=$H$2),B1,""
6. Copy C1 down as far as you want (in your example to C6
7. In H4 put =SUM(C:C)

wHY?
1. Gives the current month
3. Works out the date that is the 11th of last month
4. Works out the date that is the 10th of this month
5. Looks at the date in A1 and checks if it is between or equal to the min or max dates. If so it copies the date, if not is shows a blank
7. Sums the results of 5

HTH

Miles

[Jinx]

Davi said:

Many Thanks Jinx, email sent, though I have had some troubles with the profile emails in the past - if you dont receive it mine is dongledell AT hotmail.com.

ETA elegance is never a concern with me, I cant even get to the working bit

Edited by Davi on Thursday 31st August 13:33

Didn't get your mail but I've sent an email to your hotmail account.

[Davi]

Well, I couldn't get either way to work pefectly within my spreadsheet, probably due to it being slightly more complex than I indicated, however with a little bit of jiggling and taking elements from both you guys answers it's now bloomin perfect, which is going to save me shed loads of time each month Many Thanks

[Jinx]

Glad to help - makes a change from just posting nonsense in the P&P

[Miles_stylus]

also glad to help