Referencing cells according to date

[Veronica Johnson]

I have 12 cells, one for each month of the year. I need each of the
cells to reference the dates on other worksheets in order to summarize
my data. For instance, my profit summary should show the profit for
January in cell J8 of one worksheet, but only if the PO date on
another worksheet is in January. February should show the profit only
if the PO is in Feb, and so on. Probably a very easy task, but I
can't seem to find it in excel help. Can anybody help on this one?
Thank you in advance.

 

[Mike H]

Veronica,

With 12 dates in A1 to A12 each of which are the months you want i.e
1/1/2008
1/2/2008
..
..
..
1/12/2008

Put this in b1 and drag down to b12
=SUMPRODUCT(--(MONTH(Sheet2!A1:A30)=MONTH(A1)),--(Sheet2!B1:B30))
The will look on Sheet 2 a1 to a30 for the month in column A and sum
corresponding cells in Column B. Extend the ranges as you require but they
must be the same length.

Mike

 

[Mike H]

correction

=SUMPRODUCT(--(MONTH(Sheet2!$A$1:$A$30)=MONTH(A1)),--(Sheet2!$B$1:$B$30))

 

[Herbert Seidenberg]

Try Pivot Table.
No formulas needed.
Comes with chart.
Excel 2003
http://www.freefilehosting.net/download/3b3ff

 

[Veronica Johnson]

Veronica,

With 12 dates in A1 to A12 each of which are the months you want i.e
1/1/2008
1/2/2008
.
.
.
1/12/2008

Put this in b1 and drag down to b12
=SUMPRODUCT(--(MONTH(Sheet2!A1:A30)=MONTH(A1)),--(Sheet2!B1:B30))
The will look on Sheet 2 a1 to a30 for the month in column A and sum
corresponding cells in Column B. Extend the ranges as you require but they
must be the same length.

Mike





- Show quoted text -

Mike,
Thank you for your response, but I don't have the authority to change
the format of any of the sheets except the one I'm creating. I have
to use the date formats which are already in place, which is ex: 26-
Jan-2008. This date is in (Order!E3). I have 12 cells in (Summary!
J....8, 10, 12, 14... so on down to J30, for each month of the year).
So, because Summary!J8, is the January Profit, I need to be able to
reference the cell (Order!E3) to see if the order date was in
January. If so, I need Summary!J8, to reflect the Profit for that
order which is in (Order!I40). Is there some way to have a formula
where, if the date in (Order!E3) contains the text "Jan", then the
amount in (Order!I40) automatically shows in (Summary!J8). If the
date in (Order!E3) contains the text "Feb", then the amount in (Order!
I40) automatically shows in (Summary!J10). "Mar" shows the amount in
(Order!I40), in (Summary!J12) and so on..... I hope I'm explaining
this thoroughly enough. I know how much information can get lost in
these forums. Is this easy/possible?