Sum based on current month

[Petzl]

Hi,

I am looking for a solution that will allow me to sum my hours by current month. I have the following scenario:

Date Hours
11/11/03 5
10/10/03 4
5/5/03 2
10/10/03 1
11/11/03 7
10/10/03 8

I want a formula the will tell me that for the current month, I have completed 12 hours. Can this be done using offset or what??

 

[xlbo]

try this:
=SUMPRODUCT((MONTH(A2:A7)=MONTH(NOW()))*(B2:B7))

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Petzl]

Thanks Xlbo....exactly what I needed! Would there be a similiar solution for the current week??? I notice there is no week function.....I assume it gets a lot more complex?

 

[xlbo]

=SUMPRODUCT((WEEKNUM(A2:A7)=WEEKNUM(NOW()))*(B2:B7))

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Petzl]

Geoff,

Damn...that was quick....Thx. It did not work and then I discovered I had to go to the add-ins for the analysis tool pak to get the weeknum function! Still getting a #value! error though....=WEEKNUM(NOW()) on it's own works fine. =WEEKNUM(A154) gives current week. Total formula is breaking down though....any ideas??

Nicho

 

[xlbo]

Hmmm - looks like weeknum can't be used in array formulae

Workaround is to put =weeknum(A2) in col C and then use
=SUMPRODUCT((C2:C7=WEEKNUM(NOW()))*(B2:B7))

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Petzl]

Brilliant Geoff! Works like a treat...might actually be able to track my hours now!!

Much appreciated!

Nicho....

 

[SkipVought]

Hi,

As an alternative, you could use a PivotTable (VERY POWERFUL!)

Sum the hours and Group the Dates by Month and Year.

To make your table dynamic, name the table range using the OFFSET function in Insert/Name/Define and us that Name in the PivotTable source data range reference.

Assuming that your table starts in A1 with a row of headings, which is mandatory...

=offset(Sheet1!$A$1,0,0,counts(sheet1!$A:$A),counta(Sheet1!$1:$1))

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com