sum working hours each month (sum hours in the last row for a monthin a table with dates)

[Pete]

I've tried a lot of formulas using SUMIF, SUMIFS, SUMPRODUCT and other functions, but I couldn't figure out how to get them to work.

By now the only solution I found is adding an extra redundant column C and hiding duplicates with conditional formatting.

# A B C D
1 3/31/13 7.5 =MONTH(A1) =SUMIF(C1:C3,MONTH(A1),B1:B3)
2 4/1/13 8.5 =MONTH(A2) =SUMIF(C1:C3,MONTH(A2),B1:B3)
3 4/2/13 8.5 =MONTH(A3) =SUMIF(C1:C3,MONTH(A3),B1:B3)

What I want to have is something like this:

# A B C
1 3/31/13 7.5 Mar: 7.5h
2 4/1/13 8.5
3 4/2/13 8.5 Apr: 17.0h


I also considered Pivot Tables (maybe that could do the job) and VBA, but I'd like to have a formula-based soulution.

Does anybody have an idea how to do that?

Thanks
-Pete

 

[Claus Busch]

Hi Pete,

Am Sun, 28 Apr 2013 15:55:52 -0700 (PDT) schrieb Pete:

# A B C D
1 3/31/13 7.5 =MONTH(A1) =SUMIF(C1:C3,MONTH(A1),B1:B3)
2 4/1/13 8.5 =MONTH(A2) =SUMIF(C1:C3,MONTH(A2),B1:B3)
3 4/2/13 8.5 =MONTH(A3) =SUMIF(C1:C3,MONTH(A3),B1:B3)

What I want to have is something like this:

# A B C
1 3/31/13 7.5 Mar: 7.5h
2 4/1/13 8.5
3 4/2/13 8.5 Apr: 17.0h

I would prefer a Pivot table
But if you want a formula, try:
=TEXT(A1,"MMM")&": "&SUMPRODUCT(--(MONTH(A1:A100)=MONTH(A1)),B1:B100)


Regards
Claus Busch

 

[Pete]

Hi Pete,



Am Sun, 28 Apr 2013 15:55:52 -0700 (PDT) schrieb Pete:












I would prefer a Pivot table

But if you want a formula, try:

=TEXT(A1,"MMM")&": "&SUMPRODUCT(--(MONTH(A1:A100)=MONTH(A1)),B1:B100)





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Hi, thanks for the quick reply. I pretty sure that I've tried exactly the same formula, but it didn't work. Maybe a typo, or maybe it wasn't the same.... I don't know...

Nevertheless, some more questions:
I thought that MONTH only accepts a single value (a serial number that represents a date), thus a construction like MONTH(A1:A3) would result in an error, since Excel doesn't know which value to take (A1, A2, A3, ...). So howcomes that this formula does the right thing?

This example gives an error.
# A B C
1 3/31/13 7.5 =SUMIF(MONTH(A1:A3),MONTH(A1),B1:B3)
2 4/1/13 8.5 =SUMIF(MONTH(A1:A3),MONTH(A2),B1:B3)
3 4/2/13 8.5 =SUMIF(MONTH(A1:A3),MONTH(A3),B1:B3)


How can I use formulas that expect a single value with Ranges? My aim is touse a single formula wherever possible (instead of using an extra helper column) like in conditional formatting where you just have to set the starting cell and Excel then evaluates the formula for the destination range. Something like "FOR Cells IN Range APPLY Formula" .I'm not sure if you know what I mean.

for example: Instead of an extra Column that can later be used as Range "B1:B2"
# A B
1 3/31/13 =MONTH(A1)
2 4/1/13 =MONTH(A2)
...
something like that: ={MONTH(A1),MONTH(A2),MONTH(A3)}
or as mathematical notation: C1=(MONTH(x), x={A1:A3})
or simply MONTH(A1:A3)

 

[Claus Busch]

Hi Pete,

Am Mon, 29 Apr 2013 09:39:59 -0700 (PDT) schrieb Pete:

This example gives an error.
# A B C
1 3/31/13 7.5 =SUMIF(MONTH(A1:A3),MONTH(A1),B1:B3)
2 4/1/13 8.5 =SUMIF(MONTH(A1:A3),MONTH(A2),B1:B3)
3 4/2/13 8.5 =SUMIF(MONTH(A1:A3),MONTH(A3),B1:B3)

please have a look:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!191
for the workbook "Pete".


Regards
Claus Busch