sumif for month calculation

[syed_mushraf]

I have different dates in col A1:A25 and in Col B1 any date is written. I want to use sumif function to get sum of Col D1:D:25 in the corresponding months total written in B1.


Col A Col B = 20/02/08 Col D

01/02/08 15
10/02/08 15
15/05/08 10
25/06/08 11
17/02/08 15
18/04/08 25


Result should come for the month of Feb is = 45

i rquired SUMIF(MONTH(A1:A25),MONTH(B1),D1:D25) but it does not work. Please help thanks.

 

[dafan]

Enter this with Ctrl-Shift-Enter else it won't work.

=SUM(IF(MONTH(A1:A25)=MONTH(B1),B1:B25,""))

Could be done with SUMPRODUCT I reckon but I couldn't come up with it.

 

[Aladin Akyurek]

Some options...

[A]

=SUMIF($A$1:$A$25,">="&B1-DAY(B1)+1,$D$1:$D$25)-
   SUMIF($A$1:$A$25,">"&EOMONTH(B1,0),$D$1:$D$25)

=SUMPRODUCT(
  --($A$1:$A$25-DAY($A$1:$A$25)+1=B1-DAY(B1)+1)
  $D$1:$D$25)

[C]
Control+shift+enter, not just enter...

=SUM(IF($A$1:$A$25-DAY($A$1:$A$25)+1=B1-DAY(B1)+1,$D$1:$D$25))

 

[dafan]

Formula above, change B1:B25 to D1:D25, sorry.

Here we go, sumproduct solution:

=SUMPRODUCT((MONTH(A1:A25)=MONTH(B1))*(D1:D25))

 

[syed_mushraf]

It does not work. It shows the error #value...

 

[Aladin Akyurek]

It does not work. It shows the error #value...

What shows the #VALUE! error?

 

[syed_mushraf]

Hats off to you.. all works fine. Thanks to mr.excel

 

[syed_mushraf]

One more exciting thing is required that will reduce a lot of time consuming work for me.

Col A Col B Col D
01/01/08 Wk1 10
02/01/08 Wk1 10
03/01/08 Wk1 10
04/01/08 Wk1 10
05/01/08 Wk1 10
06/01/08 Wk2 20
07/01/08 Wk3 30
31/01/08 Wk4 25
01/02/08 Wk5 15
02/02/08 Wk5 20
03/02/08 Wk5 25
28/02/08 Wk8 10
....
....

my requirement is that if enter a date in cell C1=04/01/08 it should give me the total of
Wk1 todate of Col D. Result would come is C2=40. If i change the date in C1=05/01/08 result would be C2=50. What formula should be used to get the desired results.

Thanks in advance.

 

[Aladin Akyurek]

one More Exciting Thing Is Required That Will Reduce A Lot Of Time Consuming Work For Me.

Col A Col B Col D
01/01/08 Wk1 10
02/01/08 Wk1 10
03/01/08 Wk1 10
04/01/08 Wk1 10
05/01/08 Wk1 10
06/01/08 Wk2 20
07/01/08 Wk3 30
31/01/08 Wk4 25
01/02/08 Wk5 15
02/02/08 Wk5 20
03/02/08 Wk5 25
28/02/08 Wk8 10
....
....

My Requirement Is That If Enter A Date In Cell C1=04/01/08 It Should Give Me The Total Of
Wk1 Todate Of Col D. Result Would Come Is C2=40. If I Change The Date In C1=05/01/08 Result Would Be C2=50. What Formula Should Be Used To Get The Desired Results.

Thanks In Advance.

Try...

=SUMPRODUCT(--($A$2:$A$100<=C1),--($B$2:$B$100="Wk1"),$D$2:$D$100)

 

[syed_mushraf]

"<=" will sum the all data. I would required it to restrict it only within the coressponding week against the date mentioned in C2=05/01/08 Wk1. Means week specific data should be added.