group data

[ashgull80]

hi
i have a sheet with income data and the date the income was recieved.
on another sheet i would like a cell to group the data for each month together.
so a1 will be jan, a2 feb, a3 march ect ect.
but the cell will need to find the all the income for january then take its data from the corrosponding cell.
eg: sheet 1
A B
date Ammount
01 jan 50
03 jan 75
05 feb 20
10 feb 60

sheet 2
A B
jan 125
feb 80

thanks for your help
ash

[CatDaddy]

=SUMPRODUCT((A:A = "jan")*(B:B))

[ashgull80]

this doesnt seem to have worked i dont think i explained it very well.
what does this formula you provided do?
thanks

[mancubus]

hi.

create a pivot table.
(date as "row label" and amount as "value")

right click on dates in pivot, click "group", select "month" from grouping box.

[ashgull80]

the problem is i have sheet where all the income is input, then on another sheet is the total input for each month in a seperate cell.

[mancubus]

attached is an example of what i said in post #4

[ashgull80]

works brilliant i completly misunderstood before.
thanks so much for your help.
is there a way of sorting it into months but keep the years seperate, so groups april 2012 seperate to april 2013 or is it best to make a new pivot table?

[mancubus]

hi.

create a pivot table.
(date as "row label" and amount as "value")

right click on dates in pivot, click "group", select "month" from grouping box.


create a pivot table.
date as "row label" and date as "column label", and amount as "value"

select both "year" and "month" from grouping box for row and column values separately.

(just click one after another.)

[mancubus]

pivot and formula solutions. sample file attached.