Return data in range for current & previous months

[Petzl]

Hi,

I would like to get 3 figures from a current spreadsheet in Excel 2003 I use to track my hours.

01/01/05 10 $100
02/01/05 8 $100
01/02/05 12 $100
22/02/05 14 $100
29/03/05 7 $100

The above is an example of the number of hours worked per day and $. I would like to total the hours worked so far this month, this month minus 1 and this month minus 2.

This will give me a total number of hours worked so far this month and a comparison for the previous 2 months.

I assume calculating the dollars will be simple once the first formula is known.

Any suggestions would be greatly appreciated!

 

[SkipVought]

Hi,

Columns a, b, c
[tt]
date qty amt
01/01/05 10 $100
02/01/05 8 $100
01/02/05 12 $100
22/02/05 14 $100
29/03/05 7 $100

[/tt]
[tt]
F1: =TODAY()
F2: =DATE(YEAR($F$1), MONTH($F$1), 1) G2: =DATE(YEAR($F$1), MONTH($F$1)+1, 0)
F3: =DATE(YEAR($F$1), MONTH($F$1)-1, 1) G3: =DATE(YEAR($F$1), MONTH($F$1), 0)
F4: =DATE(YEAR($F$1), MONTH($F$1)-2, 1) G4: =DATE(YEAR($F$1), MONTH($F$1)-1, 0)

H2: =SUMPRODUCT((A2:A6>=F2)*(A2:A6<=G2)*(B2:B6)*(C2:C6))
H3: =SUMPRODUCT((A3:A7>=F3)*(A3:A7<=G3)*(B3:B7)*(C3:C7))
H4: =SUMPRODUCT((A4:A8>=F4)*(A4:A8<=G4)*(B4:B8)*(C4:C8))

[/tt]


Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[Petzl]

Excellent.....thanks Skip! I need a variation of what you posted as I want separate figures for hours and $ but I can see how you get the sums and can make the variations myself I think!

Petzl.....