formula
 

sheet 1 is my data as given below
A B C
D
AMOUNT DATE CUSTOMER NAME
COLLECTION BOY
200 1/1/06 ANIL
SUNIL
200 1/1/06 AMIT
SHANKAR
200 2/1/06 PRADEEP
SUNIL
200 2/1/06 JON
SHANKAR
200 3/1/06 RAJ
SUNIL
200 3/1/06 AMIT
SHANKAR
200 1/1/06 ANIL
SUNIL
200 1/1/06 AMIT
SHANKAR
200 2/1/06 PRADEEP
SUNIL
200 2/1/06 JON
SHANKAR

etc.....



result must be like this .i have a sheet 2 name sunil
A B
C
DATE AMOUNT NOS
OF CUSTOMER
1/1/06 400
2
2/1/06 400
2
3/1/06 200
1

SAME RESULT IN SHEET 3 NAME SHANKAR
A B
C
DATE AMOUNT NOS
OF CUSTOMER
1/1/06 400
2
2/1/06 400
2
3/1/06 200
1


when i feed the date in column a in sheet2(sunil) and in sheet3(shankar) the
amount and nos of customer on that date must be displayed.
i had used sumproduct but it does't work.
pls help me.

--
Message posted via http://www.officekb.com

formula
 

Enter the "Collection Boy" name in D1 of Sheet2 and Sheet3.

On Sheet2 and Sheet3, enter this in B2, the "Amount" column:

=SUMPRODUCT((Sheet1!B$2:B$11=A2)*(Sheet1!D$2:D$11= D$1)*Sheet1!A$2:A$11)

NOW ... when you say that in the second sheet, you're looking for the number
of customers, it seems that's *not* what you want.
You have *only 1 customer* for Sunil (Anil), but your example shows 2.
That's the number of *transactions*, not unique customers.

Following your example, this will give the number of transactions that
occurred in the "Number of Customers" column:

On Sheet2 and Sheet3, enter this in C2, the "Number of Customers" column:

=SUMPRODUCT((Sheet1!B$2:B$11=A2)*(Sheet1!D$2:D$11= D$1))

Select *both* B2 and C2, and copy down as needed.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"hitesh via OfficeKB.com" <u26634@uwe wrote in message
news:66c6023fdc004@uwe...
sheet 1 is my data as given below
A B C
D
AMOUNT DATE CUSTOMER NAME
COLLECTION BOY
200 1/1/06 ANIL
SUNIL
200 1/1/06 AMIT
SHANKAR
200 2/1/06 PRADEEP
SUNIL
200 2/1/06 JON
SHANKAR
200 3/1/06 RAJ
SUNIL
200 3/1/06 AMIT
SHANKAR
200 1/1/06 ANIL
SUNIL
200 1/1/06 AMIT
SHANKAR
200 2/1/06 PRADEEP
SUNIL
200 2/1/06 JON
SHANKAR

etc.....



result must be like this .i have a sheet 2 name sunil
A B
C
DATE AMOUNT
NOS
OF CUSTOMER
1/1/06 400
2
2/1/06 400
2
3/1/06 200
1

SAME RESULT IN SHEET 3 NAME SHANKAR
A B
C
DATE AMOUNT
NOS
OF CUSTOMER
1/1/06 400
2
2/1/06 400
2
3/1/06 200
1


when i feed the date in column a in sheet2(sunil) and in sheet3(shankar)
the
amount and nos of customer on that date must be displayed.
i had used sumproduct but it does't work.
pls help me.

--
Message posted via http://www.officekb.com

formula
 

Ragdyer wrote:
Enter the "Collection Boy" name in D1 of Sheet2 and Sheet3.

On Sheet2 and Sheet3, enter this in B2, the "Amount" column:

=SUMPRODUCT((Sheet1!B$2:B$11=A2)*(Sheet1!D$2:D$11 =D$1)*Sheet1!A$2:A$11)

NOW ... when you say that in the second sheet, you're looking for the number
of customers, it seems that's *not* what you want.
You have *only 1 customer* for Sunil (Anil), but your example shows 2.
That's the number of *transactions*, not unique customers.

Following your example, this will give the number of transactions that
occurred in the "Number of Customers" column:

On Sheet2 and Sheet3, enter this in C2, the "Number of Customers" column:

=SUMPRODUCT((Sheet1!B$2:B$11=A2)*(Sheet1!D$2:D$11 =D$1))

Select *both* B2 and C2, and copy down as needed.

sheet 1 is my data as given below
A B C
[quoted text clipped - 52 lines]
i had used sumproduct but it does't work.
pls help me.



thks--------------Ragdyer

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200609/1

formula
 

Appreciate the feed-back.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"hitesh via OfficeKB.com" <u26634@uwe wrote in message
news:66d2944646836@uwe...
Ragdyer wrote:
Enter the "Collection Boy" name in D1 of Sheet2 and Sheet3.

On Sheet2 and Sheet3, enter this in B2, the "Amount" column:

=SUMPRODUCT((Sheet1!B$2:B$11=A2)*(Sheet1!D$2:D$11 =D$1)*Sheet1!A$2:A$11)

NOW ... when you say that in the second sheet, you're looking for the
number
of customers, it seems that's *not* what you want.
You have *only 1 customer* for Sunil (Anil), but your example shows 2.
That's the number of *transactions*, not unique customers.

Following your example, this will give the number of transactions that
occurred in the "Number of Customers" column:

On Sheet2 and Sheet3, enter this in C2, the "Number of Customers" column:

=SUMPRODUCT((Sheet1!B$2:B$11=A2)*(Sheet1!D$2:D$11 =D$1))

Select *both* B2 and C2, and copy down as needed.

sheet 1 is my data as given below
A B
C
[quoted text clipped - 52 lines]
i had used sumproduct but it does't work.
pls help me.



thks--------------Ragdyer

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200609/1