Month wise Sum

[hsg]

My data has following structure:

A B C D
3-Jul-09 25 =month(a1) =sumif($c$1:$c$50,"=1",$a$1:$a$50)
5-Jan-09 34 =month(a2) =sumif($c$1:$c$50,"=2",$a$1:$a$50)
:
:
:
:
:
and so on ...

Column A will have several dates. Column B will have data which is to be
summed.
Currently I am suing sumif in D1 to D12, one cell for each month. D1 for
January
total, D12 for December total. To use sumif, I have to add additional column
"C",
which lists "=month(A)" in corresponding row. I am presently using column
"DD" in place of column "C" so that the monthvalue remains hidden.

Can this Column C be eliminated and monthvalue of Column A extracted in the
formulat in Column D itself?

thanks

 

[Bob Umlas]

=SUMPRODUCT(--(MONTH(A$1:A$50)=ROW(A1)),B$1:B$50)
and fill down 12 rows
Bob Umlas
Excel MVP

 

[hsg]

Dear Bob,

Thanks a lot. Works perfect!

=SUMPRODUCT(--(MONTH(A$1:A$50)=ROW(A1)),B$1:B$50)
and fill down 12 rows
Bob Umlas
Excel MVP



.

 

[hsg]

What do the two hyphens stand for? I checked other examples also,
there are many array formulas which indicate "(--(" kind of steps.
When I removed the two hyphens, I got 0 as result. Help in excel
array formula does not mention anything about hyphens.

 

[Jacob Skaria]

Hi

It is called "double unary minus" and J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html