Sum data for each month

[nickester]

Hello all,

I have been attempting to solve this problem for about a week now using different combinations of functions but have not been able to produce what I wish.

Let me begin with a description of my excel sheet:
Sheet 2
Column A: Dates in the following format: 2011-8-15
These dates can repeat and or skip dates. They always increase in value however as you go down the column.

Column B: numbers in decimal notation

Sheet 1
Sheet 1 is going to be used as a summary sheet which will show the totals for each month.

I am having trouble in creating a function which will go down column a and figure out which rows in column b should be totaled in order to obtain the totals for each month. Please note that there is no predetermined number of rows of data for each month.

I would love to hear some suggestions in how I could solve this problem. Plase note that I cannot use VBA to solve this problem as the excel file will be moved from mac to windows when needed.

Thank you all for taking the time to read this.

[tlafferty]

I'd suggest using a pivot table to accomplish this. You could use sumif, but that makes your book "fragile" and constrains you to the layout you first choose when laying out your report. If you'd like an example of how it might look, please attach a sample workbook.

[nickester]

I would attach my file but have no clue how to place an attachment.

[nickester]

Never mind, just found out.

Here is my attachment

[tlafferty]

The attached has a pivot report. To produce one, click in the data you want to summarize, then click Insert=> pivot table=> new worksheet.

Right click in your dates, then click group=> months.

Note that it will reduce to a single month since that's all that your data includes.

Regards,
Tom

[nickester]

Is there any way to perform without the use of a pivot table? The users of this data are not computer savy and would like to just see a front page which would update itself as further data is entered in the future.

[tlafferty]

This will do the trick, but be aware that the formula only examines rows 2-30, so you'll want to expand that out for the future.

Regards,
Tom

[nickester]

This will do the trick, but be aware that the formula only examines rows 2-30, so you'll want to expand that out for the future.

Regards,
Tom

That was exactly what I needed Tom. Thanks so much for your help.

The function you provided me was:

=SUMPRODUCT(--(MONTH(Entry!$A$2:$A$30)=MONTH(A2)*(YEAR(Entry!$A$2:$A$30)=YEAR(A2)))*(Entry!$B$2:$B$30))

Can you help me understand:

1. What does "--" do?
2. Why can I not use "A:A" to define the entire column?

Thank you again for your help Tom.

[nickester]

Tom,

Looking at the code further, can you explain what it is doing exactly?

I see that you are obtaining boolean values to check if the month and the year match the month and year of column A in the summary sheet but assuming that both conditions return a "1", what is the last bit of the code doing ($b$2:$b$30)?

I figured out that -- converts true to 1 and false to 0

Still not sure about why A:A can't be used.

Thanks,
Nick

[tlafferty]

You can definitely use A:A. One thing you may want to look into though if you will be using data that occupies thousands of rows is something along the lines of the SUMIF formula - it uses less memory than SUMPRODUCT. The -- forces Excel to treat the trues and falses as 1 and 0. There are some really good articles on the use of SUMPRODUCT, so you may want to consider googling it.

[DonkeyOte]

You can definitely use A:A.

I'm afraid the above is not entirely true.

You can not use entire column references in Arrays/SUMPRODUCT prior to XL2007 - earlier versions will generate #NUM! errors.
FWIW, you should never use entire column references with either Arrays or SUMPRODUCT, both approaches being inefficient when compared to "normal" functions.

One thing you may want to look into though if you will be using data that occupies thousands of rows is something along the lines of the SUMIF formula

Agree with the above 100%.

In this case given it's a simple month extraction I would suggest you combine two SUMIFs per calculation:

Please Login or Register  to view this content.

The above will be much more efficient than a SUMPRODUCT approach when working with large data sets.

Regards:

Please Login or Register  to view this content.

in the above form the double unary is unnecessary given the arrays are being coerced latterly via multiplication... if you were to use SUMPRODUCT then for sake of robustness I confess I would look to use something along the lines of:

Please Login or Register  to view this content.

the above avoids explicit coercion of underlying values

[nickester]

Thank you Tom and DonkeyOte for all your help. Both solutions solve my problem for now and I understand what's going on with the code (I love that feeling).

[hillsjester]

Hello,

Thanks a lot for these useful information.
I am preparing a financial report worksheet which should produce monthly reports.
I was wondering how to use a pivot table to add monthly data from several columns.
In the example given in this thread there is the Entry Date and only one column of values.
How could a pivot give a monthly sum or count or average for several columns of different values?
Hope I am clear enough.
I am a novice with Excel and with this forum as well ;-)

Many thanks in advance

[FDibbins]

Hi, welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[hillsjester]

Oh Sorry FDibbins,
I didn't see that rule. I will start a new thread.
Thanks for guiding me.