Formula help

[Emmily]

Hi I am after a formula, that will provide a sum for each month based on the data in Col A1:D

Date	UKN	FX	TEMP
1-Nov-12	346	10	0			Nov-12	Dec-12
2-Nov-12	151	55	0		Total	2318
5-Nov-12	184	17	0
6-Nov-12	131	18	0
7-Nov-12	623	34	0
8-Nov-12	151	42	0
9-Nov-12	182	13	0
12-Nov-12	154	13	0
13-Nov-12	73	13	0
14-Nov-12	65	43	0

<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" span=7 width=64><TBODY>
</TBODY>

 

[MARK858]

Try

G3=SUMPRODUCT((MONTH($A$2:$A$11)=11)*(YEAR($A$2:$A$11)=2012),($B$2:$B$11)+($C$2:$C$11)+($D$2:$D$11))


H3=SUMPRODUCT((MONTH($A$2:$A$11)=12)*(YEAR($A$2:$A$11)=2012),($B$2:$B$11)+($C$2:$C$11)+($D$2:$D$11))

Obviously the formula is from A2 not A1 because of the title row and I would have used SUMIFS but would have needed to use specific dates which could become a cumbersome formula if you have too many months but would be more efficient.

 

[MARK858]

Also see Aladins post in the other thread (I do realise there were some problems with the board time).

http://www.mrexcel.com/forum/excel-questions/747388-formula-help.html#post3672407

Which takes the months from your text and should be more efficient than SUMPRODUCT.

 

[Jon von der Heyden]

Yup I'm locking this one down because it is a dupe. Please stick with your original. Thanks.