Add columns within monthly date ranges

[essee]

Hi guys,

I would very much appreciate some help with adding up columns relating to a specific month range.

My spread sheet lists dates in column A and a number of columns, say 6, with numerical data (see example attached).

I want to be able to select on another sheet the month and add up the data and display it on one row of 6 columns.

(I realise I can just select the cells, but I want to copy across a date formula to many other spreadsheets, and I don't want to have to worry about the month length (30th/31st)

Many thanks,
David.

[benishiryo]

hi David, could you standardise the way months are input in "Summary". i would suggest it in a proper date format like 1-Aug-2012. you can then format cells to show as Aug-12 by Custom format "mmm-yy". but if you want just the text, maybe standardise it as 3 letters or full text. for 3 letters, you can use:
=SUMPRODUCT((TEXT(Data!$A$2:$A$92,"mmm")=$A2)*(Data!B$2:B$92))

for full spelling of months, try:
=SUMPRODUCT((TEXT(Data!$A$2:$A$92,"mmmm")=$A2)*(Data!B$2:B$92))

the reason why i suggested a proper date format is because the above formulas wont recognise the years. this formula's for a proper date format:
=SUMPRODUCT((TEXT(Data!$A$2:$A$92,"mmm-yy")=TEXT($A2,"Mmm-yy"))*(Data!B$2:B$92))

[essee]

Hello again!

Hmm.. the date format on the Summary sheet is not too important, so your suggestions are fine, but I can't seem to get this working.

Are you adding up the dates?

[essee]

Actually, ignore this. I've gone a different way now! Thanks for you time and help again, benishiryo, but I may return to this at a different date.

Do I mark this as resolved or just delete it or anything? Thanks.

[benishiryo]

always glad to help. i was actually saying you should change to either of the 3 for it to work. but if you insist on it, at least 3 of the 1st letters would work too for this formula:
=SUMPRODUCT((TEXT(Data!$A$2:$A$92,"mmm")=LEFT($A3,3))*(Data!B$2:B$92))

do mark this resolved if there are no further questions on this scenario. thanks