formula

[hitesh via OfficeKB.com]

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.

 

[Ragdyer]

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$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$11=D$1))

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

 

[hitesh via OfficeKB.com]

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$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$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

 

[RagDyeR]

Appreciate the feed-back.
--

Regards,

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

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$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$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