Excel Add Month Totals

[Happy Harry]

Hi Guys
An Excel question for anyone
I have a spreadsheet that I keep track of my tax payments to my employees
To make it slightly easier to transfer this info directly to IRD sheets I wish to be able to add payments for each individual month.
Example below

B6 has “Paid Date”, C6 has “Gross”, D6 Has “Net” and E6 has “Tax paid”

What I am trying to do (unsuccessfully) is to have in column F show the cumulative Tax paid for that calendar month. As I pay fortnightly most months have two lots of tax to pay per employee and some have three lots of tax to pay per employee If the last pay date is 24/6/09 I want the cumulative tax paid for the month in Column E, to show Column F in Row 24/6/09
Is there a formula to use to add payments made in month i.e. Jan, Feb March etc.

I hope this makes sense and someone has a solution.
Cheers
HH

[nofam]

Pivot tables are the answer - if you add the dates as a row field, and the tax paid as a data field, you can group the individual dates into months.

There are other ways, but this is probably the easiest.

[kahawai chaser]

I agree - Pivot tables, I have used extensively,and can arrange(drag) in a variety of display modes (row-column formats). Can also easily create charts from them (right click for pivot chart), and apply formulas (right click on results). But generally useful for a large amount of data with the same groups and sub groups.

I think data consolidation tables might also work, if your work sheets/work books are of the same design/layout (and same range), for example by day, week, month etc. Thus you can create a summary table, based on your same design input (employees) tables. Search for online tutorials

[andrew93]

Hi

You can do this with a formula if you set up your data like this:

B7:B16 = dates (extend as needed)
E7:E16 = taxes deducted (ditto)
G7 = from date (e.g. 1/1/09)
H7 = to date (e.g. 15/1/09)

Use the formula :
=SUMPRODUCT(--(B$7:B$16>=G7), --(B$7:B$16<=H7), E$7:E$16)

to get the sum of taxes deducted between a range of dates. This formula is designed to be copied down if you want to see the values for consecutive periods, but if you do that then you need to set the periodic dates in columns G & H below the 2 values above.

Set the ranges B$7:B$16 and E$7:E$16 to the correct number of rows for your data.

Andrew