How do I list all sums from sheet1 to sheet2?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

neilg_cebu - 17 Mar 2006 03:13 GMT

Hi All,

As the subject. I would like to list all sums in sheet1 to sheet2
Example:
Sheet1:
Date Amount
01/01/2006 1000
01/01/2006 1500
01/01/2006 500
01/01/2006 800
Total 3800

01/02/2006 2000
01/02/2006 3000
01/02/2006 2000
Total 7000

Sheet2:
01/01/2006 3800
01/02/2006 7000

I want sheet2 to be automatically filled-in with values from sheet1 a
illustrated above.

Is it possible to do it? Please help!

Thanks,
Nei

--
neilg_cebu

Reply to this Message

Max - 17 Mar 2006 11:26 GMT

Here's one way to achieve it using non-array formulas

See the sample construct at:
http://www.savefile.com/files/7929775
AutoList Daily Totals from Sheet1 to Sheet2.xls

As you are posting/reading from Excelbanter, pl note that the formulas
described below may not appear properly in Excelbanter. Think Excelbanter
unfortunately removes all "greater than" or "less than" signs from posts, so
any formulas which happen to contain these operators will not appear
correctly [for eg: the COUNTIF(...) in the formula in C1 below].
Pl download/see the sample construct above.

Assume table as posted is
in Sheet1's cols A and B, from row1 down

In Sheet2,

Put in A1:
=IF(ISERROR(SMALL(C:C,ROW())),"",
INDEX(Sheet1!A:A,MATCH(SMALL(C:C,ROW()),C:C,0)))
Format A1 as date

Put in B1:
=IF(A1="","",SUMIF(Sheet1!A:A,A1,Sheet1!B:B))

Put in C1:
=IF(OR(Sheet1!A1="",ISTEXT(Sheet1!A1)),"",IF(COUNTIF(Sheet1!$A$1:A1,Sheet1!A
1)>1,"",ROW()))

Select A1:C1, fill down to say, C100,
to cover the max expected extent of data in Sheet1's col A

Sheet2 will auto-return the required results from Sheet1
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Hi All,
>
[quoted text clipped - 27 lines]
> --
> neilg_cebu

Reply to this Message