sumif for month calculation

syed_mushraf

Active Member
Joined
Oct 13, 2002
Messages
261
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.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Enter this with Ctrl-Shift-Enter else it won't work.
Code:
=SUM(IF(MONTH(A1:A25)=MONTH(B1),B1:B25,""))
Could be done with SUMPRODUCT I reckon but I couldn't come up with it.
 
Upvote 0
Some options...

[A]
Code:
=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)


Code:
=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...
Code:
=SUM(IF($A$1:$A$25-DAY($A$1:$A$25)+1=B1-DAY(B1)+1,$D$1:$D$25))
 
Last edited:
Upvote 0
Formula above, change B1:B25 to D1:D25, sorry.

Here we go, sumproduct solution:
Code:
=SUMPRODUCT((MONTH(A1:A25)=MONTH(B1))*(D1:D25))
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
"<=" 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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top