Help in conditional SUM

[Sunny]

Hi all,

I have two sheets in my spreadsheet. On first sheet I want to display
summarray of second sheet.

Here is the example:

Sheet1:

Month Sales
January 425.00
February 300.00
March 0.00
April 400.00
....
....
....
Total

Sheet2
SaleDate Amount
1/3/03 100.00
1/5/03 200.00
1/10/03 125.00
2/3/03 25.00
2/10/03 275.00
4/5/03 250.00
4/15/03 150.00
....
....
....

Can anyone give me idea?

Thanks.

 

[Don Guillett]

try this for January
=sumproduct((month(sheet2!range("a2:a200")=1)*sheet2!range("b2:b200)
or if formula in row 1. Just copy down
=sumproduct((month(sheet2!range("a2:a200")=row())*sheet2!range("b2:b200)
or modify row() to row(a1) if starting formula is on another row

 

[Aladin Akyurek]

Have a look at pivot tables.
With formulas...

Let A1:B8 on Sheet2 house your sample including labels.

Let A1:A13 on Sheet1 house the full month names including the label "Month".

In B2 enter & copy down:

=SUMPRODUCT(--(TEXT(Sheet2!$A$2:$A$8,"dddd")=A2),Sheet2!$B$2:$B$8)

Note that this formula does not test the year involved, that is, it will
happily include all january sales regardless the year.

 

[Sunny]

Thanks Don and Aladin! Works great!

 

[Sunny]

When I insert one row in sheet2, it wont update formula in sheet1. I was
hopping it should change a2:a201 (201st row added)

Thanks.

 

[Don Guillett]

Insert at row 200 or before for auto expansion. Or use a defined name for
each range
name rngA
refers to =offset(#A$2,0,0,counta($A:$A)+1,0)

 

[Sunny]

Thanks again. I inserted after row 200. When I insert before 200, it works
fine.