# sumif for month calculation

#### syed_mushraf

##### Active Member
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

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### dafan

##### Well-known Member
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.

##### MrExcel MVP
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:

#### dafan

##### Well-known Member
Formula above, change B1:B25 to D1:D25, sorry.

Here we go, sumproduct solution:
Code:
``=SUMPRODUCT((MONTH(A1:A25)=MONTH(B1))*(D1:D25))``

#### syed_mushraf

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

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

What shows the #VALUE! error?

#### syed_mushraf

##### Active Member
Hats off to you.. all works fine. Thanks to mr.excel

#### syed_mushraf

##### Active Member
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.

##### MrExcel MVP
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.

Try...

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

#### syed_mushraf

##### Active Member
"<=" 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.

Replies
6
Views
731
Replies
2
Views
293
Replies
2
Views
468
Replies
5
Views
409
Replies
13
Views
520

1,191,166
Messages
5,985,051
Members
439,935
Latest member
Monty238

### 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.

### Which adblocker are you using?

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

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