SUM IF DATE IN IN THAT MONTH

[superstarr]

hi
need a little hlp pls
have a range of dates, ie 3/11/2004
would like to have the sum of all the dates by month
tried the sumif, but cant figure out how to tell formula that 3/11/2004 is in march so add to the march total

thx much

JOn

 

[Andrew Poulsom]

Try something like this:

=SUMPRODUCT((MONTH(A1:A5)=3)*(B1:B5))

Change the range references to suit.

 

[dtaylor]

Hi There

In the column next to your dates insert - assuming dates in column A
=Month(A1) this converts the date to a number

Then to summarize

=SUMIF(B1:B6,1,C1:C6)
This will give the total for Janurary, which will = 1
now do one sumif for each month changing the one.
=SUMIF(B1:B6,2,C1:C6)
=SUMIF(B1:B6,3,C1:C6)

ect....


Dan

 

[Aladin Akyurek]

Are you trying to count Jan-dates, Feb-dates, etc. in a given year?

 

[superstarr]

looks great
thnaks very much

 

[mrxlsx]

Andrew Poulsom,

You are a lovely guy.. I have seen you giving amazing solutions..Thank you!
mrxlsx
_______________________________________________________________
"Real knowledge is to know the extent of one's ignorance".....Confucius

 

[Yahya]

try it also by Array formula

Excel 2007
	A	B	C	D	E	F
1	Date	Sales			Date	Sum
2	01/01/2013	313			01/07/2013	1531
3	16/01/2013	629
4	31/01/2013	925
5	15/02/2013	583
6	02/03/2013	907
7	17/03/2013	642
8	01/04/2013	453
9	16/04/2013	612
10	01/05/2013	568
11	16/05/2013	429
12	31/05/2013	264
13	15/06/2013	497
14	30/06/2013	994
15	15/07/2013	915
16	30/07/2013	616
17	14/08/2013	351
18	29/08/2013	668
19	13/09/2013	244
20	28/09/2013	438
21	13/10/2013	309
22	28/10/2013	664
23	12/11/2013	778
24	27/11/2013	580
25	12/12/2013	166
26	27/12/2013	863
Sheet3

Cell Formulas
Range	Formula
F2	{=SUM(IF(MONTH($A$2:$A$26)=MONTH(E2),$B$2:$B$26))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mrxlsx]

Hello Hussein,

I tried this whole night, but stuck up at giving the argument $B$2:$B$26 yesterday.. thanks anyways..Hussein

 

[atifnasrullah]

Dear Dan,

The sumif formula is not working in the excel 2007. It return 0 always.

 

[rhyse1977]

try it also by Array formula

Array Formulas Cell Formula F2 {=SUM(IF(MONTH($A$2:$A$26)=MONTH(E2),$B$2:$B$26))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Hi,

Sorry to reopen, what seems to be a closed topic, but i have hit a snag with this solution.

It has worked perfectly for all of the months, other than january, which is giving a total of all of the months combined. (i assume its, seeing the 1 as true)

Any ideas how to resolve this please?