sum

[Guest]

in sheet 1

column a column b column c
date boy amt
10/2/2006 amit 200
10/2/2006 amit 300
10/2/2006 amit 100
15/2/2006 anil 300
15/2/2006 anil 100
15/2/2006 anil 200
10/2/2006 amit 100
11/2/2006 sunil 200
11/2/2006 amit 100
11/2/2006 amit 100


result i want
in sheet 2
column a column b column c
date boy amt
10/2/2006 amit 700
11/2/2006 amit 200
15/2/2006 anil 600


so pls help on this

so i can get the amount in column c (i want the formula to calculate the
amount in second sheet. in column a date must link with column a of sheet 1 )

 

[Ragdyer]

Is it a typo that you did not include sunil for 11/2/2006, or is there
supposed to be *only* one person per date?

 

[Dave Peterson]

You may want to look at Data|pivottable to do this kind of summary.

If you want to read more about pivottables...

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

 

[Guest]

Is it a typo that you did not include sunil for 11/2/2006, or is there
supposed to be *only* one person per date?

sunil also there on 11/2/2006


(11/2/2006 how much amount is there must be refelect. same
for other dates also i am the new user of excel)

 

[Guest]

Is it a typo that you did not include sunil for 11/2/2006, or is there
supposed to be *only* one person per date?

 

[Guest]

=SUMPRODUCT(--(Sheet1!$A$2:$A$11=Sheet2!A2),--(Sheet1!$B$2:$B$11=Sheet2!B2),Sheet1!$C$2:$C$11)

Assuming header in row 1

 

[Guest]

=SUMPRODUCT(--(Sheet1!$A$2:$A$11=Sheet2!A2),--(Sheet1!$B$2:$B$11=Sheet2!B2),Sheet1!$C$2:$C$11)

Assuming header in row 1

i used u r formula but not working. its calculate only one date.

result must be like this
date boy amt count
10/2/2006 amit 700 4
11/2/2006 amit 200 2
15/2/2006 anil 600 3
etc ......