Getting column value based on month

[barryjcorrigan]

I have a spreadsheet containing
Column A: Date
Column B: Delta

E.g.,
A1: January 3rd 2006
B1: -100
A2: February 4th 2006
B2: 600
A3: February 12th 2006
B3: -200

What I would like is a monthly summary of the change

I have tried adding a sheet like this:
a1: Month Start:
a2: September 1 2005
a3: October 1 2005
a4: November 1 2005

And then:
=SUM(IF(AND(sheet1!a:a>=A2,sheet1!a:a<A3),sheet1!b:b),0) (this is
supposed to get values between September 1 2005 and October 1 2005)

But it does not work.

It only gives a value for the first month, which matches the sum of the
whole column B, not just the values where column A is in the correct
month.

Any ideas how to make this work?

 

[Guest]

If you can use defined ranges instead of full columns, you may try something
like this:
=SUMPRODUCT(--(Sheet1!$A$1:$A$100>=A1),--(Sheet1!$A$1:$A$100<A2),Sheet1!$B$1:$B$100)

Hope this helps,
Miguel.

 

[kkchoh]

Hi,

Try this array formula...

=SUMPRODUCT(SHEET1!$B$1:$B$65536,--(SHEET1!$A$1:$A$65536>=A2, --(SHEET1!$A$1:$A$65536<A3))

kk