Break down by month...

Hello All!!! :-D

I have a spreadsheet that I need to break down the numbers by month. The date is in column A and the data/numbers are in column E. How can I get Excel to select the numbers in column E based on the date in column A and then sum it all up by the month?

Thanks for all the in advance!

One option is to summarize by Pivottable.
In that case you need to insert a new column: =TEXT(A2,"YYYY-MMM"), as a helper column.

Another option is to use a separate Summary table:
=SUMPRODUCT((TEXT($A$2:$A$100',"MMMM")="January")*$E$2:$E$100)

HTH
Ola Sandström

Another option is to use a separate Summary table:
=SUMPRODUCT((TEXT($A$2:$A$100',"MMMM")="January")*$E$2:$E$100)

HTH
Ola Sandström


Thank you for posting a reply, it seems best if I make a summary table, will this formula work if the date format is DD/MM/YY?

Yes it should work.
Here's an example. But you have to change the SummaryTable into English. Januari-->January and the formula ÅÅ-->YY

And if you want to have jan/05 in the summary table, you have to enter 'jan/05

Ola Sandström


File: http://www.excelforum.com/attachment...tid=3633&stc=1

Attached is what I'm working on, I've tried to place the formula's into the summary table and I get "0" for the sum. My goal is to make charts out of all this data to keep track of whats coming in, the reject rate, and who is responsible.

It was just a minor mistake. You should have used "MMM" instead of "MMMM".
But when I was at it, I made a sample file:http://www.excelforum.com/attachment...tid=3640&stc=1
Hope it can help you.

Good luck
Ola Sandström


Btw, I saw that the Date Row 128 sheet Electronics, is not entered correct.

better yet; i would just make an additional copy of your data for every
single report that you do.. that way when you have a nice 200mb file
to email home every night..

GAG

(I AM BEING SARCASTIC, EXCEL IS _NOT_ A REPORTING TOOL. STORE YOUR
DATA IN A DATABASE AND THEN YOU CAN REPORT ON IT IN ANY SHAPE THAT YOU
WANT).

Thanks again for your help Ola!

Mr. Kempf that would be great but, for some reason I don't have access to Access, if it gets too big someone else will take over and do the dirty work.