Month wise Sum

  • Thread starter Thread starter hsg
  • Start date Start date
H

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
 
=SUMPRODUCT(--(MONTH(A$1:A$50)=ROW(A1)),B$1:B$50)
and fill down 12 rows
Bob Umlas
Excel MVP
 
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.
 
Back
Top