Create financial report by quarter and month

[adelkam]

Hello,

I have data that I need to summarize in various ways based on the "date" field.
I need to summarize the totals by quarter, month, quarter by department, etc. I am attaching a file.
I appreciate any help formula or VBA
Financial Report Summary.xlsx

[FDibbins]

If you add a helper column to show quarters, you could probably do this with a Pivit Table.

This, in I2, copied down, will give you quarters...
=IF(MONTH(E2)>9,4,IF(MONTH(E2)>6,3,IF(MONTH(E2)>3,2,1)))

Here is a sample of what it could look like, see if this is something you can work with?

[MarvinP]

Hi,

Pivots can be GROUPED by year and quarter. You shouldn't need helper columns to split these out. See the attached for an example. I believe all your tables can be done using Pivots.

[FDibbins]

Thanks for the assist Marvin, PT's are not my strong side, but I figured it was a good way to start.

Without intending to hi-jack this thread (Im sure the OP will ask this anyway) where does teh Years field come from, it it a calc'd field?

[MarvinP]

After you drag the date field down to the Rows section in the Pivot List, right click on the date in the Pivot.

THEN click on GROUP and if all the dates are real dates you can select how to group those dates.

See the picture and examples at http://www.contextures.com/xlPivot07.html

Also - they are Pivots Tables (not Pivits)

[adelkam]

UPDATE: did not see MarvinP's response before I post my follow up question below .. Now I know that I can group by month as well
I just need to make the pivot dynamic, and updated with any new data that would be added

Thanks
===================

Thanks all,
How can I create pivot table for the months? Do I need a helper column for it?
Can I set the pivot table to expand automatically if I added more data?

Thanks again

[FDibbins]

After you drag the date field down to the Rows section in the Pivot List, right click on the date in the Pivot.

THEN click on GROUP and if all the dates are real dates you can select how to group those dates.

See the picture and examples at http://www.contextures.com/xlPivot07.html

Also - they are Pivots Tables (not Pivits)

aahh good to know, thanks

And thanks for picking up on my typo

[adelkam]

Thanks FDibbins & MarvinP ,

I figured out the dynamic range as well. Thanks for your help. It appears that the pivot table is powerful. I need to learn more about it!

Thanks again

[MarvinP]

OK here you go....

I created a Dynamic Named Range on your data and called it DNRData. You need to look at the Names Manager to see how I did that.
THEN let the Pivot Table data come from this DNRData so it will span any new or removed data.
THEN I put an Event Macro behind the sheet to Refresh All pivot tables when you click on the Pivot Table sheet.

You might need to learn Event Macros and DNR's along with how Pivots can use a named range for its data. See the attached.

Change some of the data and then click on the Pivot Tab (mine) and see that the numbers will reflect the new data.

[adelkam]

Thanks alot MarvinP

This is cool!

one last question: I created another pivot table and tried to group data differently (by month), but it changes the grouping in the first pivot table as well!

It does not seem that I could group the dates differently in both pivot tables. I reattached the file

Pivot Group by Date Year Quarter Event DNR-1.xlsm

[MarvinP]

Hi,

Create a separate DNR of your exact same data for each Pivot Table view you want. Then when you change the grouping it won't affect all Pivots. See the attached where I've created DNRData2 which is exactly the same as DNRData. Base the second Pivot on this range and it does what you want.

[adelkam]

Thanks MarvinP a lot for your help!