Help in conditional SUM
 

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.

Help in conditional SUM
 

try this for January
=sumproduct((month(sheet2!range("a2:a200")=1)*shee t2!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
--
Don Guillett
SalesAid Software

"Sunny" wrote in message
...
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.

Help in conditional SUM
 

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" wrote in message
...
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.

Help in conditional SUM
 

Thanks Don and Aladin! Works great!
"Sunny" wrote in message
...
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.

Help in conditional SUM
 

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" wrote in message
...
try this for January
=sumproduct((month(sheet2!range("a2:a200")=1)*shee t2!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
--
Don Guillett
SalesAid Software

"Sunny" wrote in message
...
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.

Help in conditional SUM
 

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)

--
Don Guillett
SalesAid Software

"Sunny" wrote in message
...
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" wrote in message
...
try this for January
=sumproduct((month(sheet2!range("a2:a200")=1)*shee t2!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
--
Don Guillett
SalesAid Software

"Sunny" wrote in message
...
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.

Help in conditional SUM
 

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

"Don Guillett" wrote in message
...
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)

--
Don Guillett
SalesAid Software

"Sunny" wrote in message
...
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" wrote in message
...
try this for January
=sumproduct((month(sheet2!range("a2:a200")=1)*shee t2!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
--
Don Guillett
SalesAid Software

"Sunny" wrote in message
...
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.