Break down by month...

  • Thread starter Thread starter damon_diamond
  • Start date Start date
D

damon_diamond

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 ca
I get Excel to select the numbers in column E based on the date i
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"), a
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ö
 
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 int
English. Januari-->January and the formula ÅÅ-->YY

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

Ola Sandström


File: http://www.excelforum.com/attachment.php?attachmentid=3633&stc=

+-------------------------------------------------------------------
|Filename: Book5.zip
|Download: http://www.excelforum.com/attachment.php?postid=3633
+-------------------------------------------------------------------
 
Attached is what I'm working on, I've tried to place the formula's int
the summary table and I get "0" for the sum. My goal is to make chart
out of all this data to keep track of whats coming in, the reject rate
and who is responsible

+-------------------------------------------------------------------
|Filename: Copy of Quality Data Log '05-copy for user only.zip
|Download: http://www.excelforum.com/attachment.php?postid=3634
+-------------------------------------------------------------------
 
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).
 
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.php?attachmentid=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.


+-------------------------------------------------------------------+
|Filename: Copy of Quality Data Log '05-copy for user only.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3640 |
+-------------------------------------------------------------------+
 
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. :)
 
Back
Top