lookup date, sum up total...

  • Thread starter Thread starter Shhhh
  • Start date Start date
S

Shhhh

Hello all,

Here is my situation:
Worksheet 1

A B C D
1.)1/1/05 Data Data P&L
2.)1/2/05 Data Data P&L
..
..
..
365.) 12/31/05 Data Data P&L

............................................................

Worksheet 2

A B
1.) January =IF(Worksheet1=January, then sum all january P&L (Ignore other
months))
2.) February =IF(Worksheet1=February, then sum all February P&L (Ignore
other months))
..
..
..
12.) December =IF(Worksheet1=December, then sum all DecemberP&L (Ignore
other months))

what formula do I need to enter in Worksheet 2 column B?


Thank you all,
Shhhh
 
pui in SHEET 2 COLUMN A insted jan feb etc. type 1,2,3-----12
a1 is 1
a2 is 2 etc

then in B1 0f sheet2
try this fomula

=SUMPRODUCT((MONTH(Sheet1!$A$1:$A$365)=Sheet2!$A1)*Sheet1!$B$1:$B$365)

copy this down.


take care of brackets and dollar signs.
 
If sheet 2 holds text names use

=SUMPRODUCT(--(TEXT(Sheet1!A1:A366,"mmmm"))-A1,Sheet1!B1:B366)

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Back
Top