Please Help!!!

[Excel_Learner]

Sheet 1:
Date Item
20/09/2006 abc
20/09/2006 abc
22/09/2006 xyz
22/09/2006 abc
26/09/2006 efg

Sheet 2:
item dept
abc Dept A
xyz Dept B
efg Dept A

Sheet 3:
Date Item Payout
20/09/2006 abc 100
22/09/2006 abc 110
23/09/2006 xyz 120
25/09/2006 abc 140
26/09/2006 efg 140
This is date wise item's payout.

Based on this data I need below mentioned detail

Date Dept A (total payout) Dept
B(total Payout)
20/09/2006
21/09/2006
22/09/2006
23/09/2006
24/09/2006
25/09/2006
26/09/2006

1) Look into sheet 1 and count how many time dept A or B is there
2) Multiply that no. with payout given in sheet 3. Here comes the realy
problem. Lets say if on 20/09/06 payout is 100 rs. for abc and payout changes
on 25/09/06 then from 20th to 24th payout will be 100 rs and it will change
on 25/09/06.
Suppose it is clear to you all. Please help.

 

[iliace]

This is not very clear.

Dept A or B is not on Sheet1.

Why are you multiplying the payout instead of adding it up? The logic
that you want to accomplish is not clear.

On Sheet2, can more than one department be matched with more than one
item?

On Sheet3, can an item appear more than once for each date?

Anyway, here's what I came up with. Array-entered into cell B2 (first
below Dept A). Delete the "(total payout)" part; you can add a row
for it above, later if you really need it. Copy down and over.
Adjust references if items are added to the list, obviously, or use
named ranges. I'm assuming this result table is on Sheet4.

=SUM((Sheet1!$A$2:$A$6=Sheet4!$A2)*(Sheet1!$B$2:$B$6=INDEX(Sheet2!$A
$2:$A$4,MATCH(Sheet4!B$1,Sheet2!$B$2:$B$4,0))))*SUM((Sheet3!$C$2:$C
$6)*(Sheet3!$A$2:$A$6=Sheet4!$A2)*(Sheet3!$B$2:$B$6=INDEX(Sheet2!$A
$2:$A$4,MATCH(Sheet4!B$1,Sheet2!$B$2:$B$4,0))))

I think this does what you want.

 

[iliace]

No sorry, that won't work. The Index to find department only finds
the first instance, and therefore will not work for subsequent
items.

Let me see if I can find another way.

 

[iliace]

I would recommend the following design change to your worksheet:

Sheet1:

Date Item Dept
20/09/2006 abc =VLOOKUP(B2,Sheet2!$A$2:$B$4,2,FALSE)
20/09/2006 abc =VLOOKUP(B3,Sheet2!$A$2:$B$4,2,FALSE)
22/09/2006 xyz =VLOOKUP(B4,Sheet2!$A$2:$B$4,2,FALSE)
22/09/2006 abc =VLOOKUP(B5,Sheet2!$A$2:$B$4,2,FALSE)
26/09/2006 efg =VLOOKUP(B6,Sheet2!$A$2:$B$4,2,FALSE)


Sheet3:

Date Item Payout Dept
20/09/2006 abc 100 =VLOOKUP(B2,Sheet2!$A$2:$B$4,2,FALSE)
22/09/2006 abc 110 =VLOOKUP(B3,Sheet2!$A$2:$B$4,2,FALSE)
23/09/2006 xyz 120 =VLOOKUP(B4,Sheet2!$A$2:$B$4,2,FALSE)
25/09/2006 abc 140 =VLOOKUP(B5,Sheet2!$A$2:$B$4,2,FALSE)
26/09/2006 efg 140 =VLOOKUP(B6,Sheet2!$A$2:$B$4,2,FALSE)


Then, the following formula on Sheet4, in B2 and copy down/over:

=SUMPRODUCT(--(Sheet1!$A$2:$A$6=Sheet4!$A2),--(Sheet1!$C$2:$C$6=Sheet4!
B$1))*SUMPRODUCT(--(Sheet3!$D$2:$D$6=Sheet4!B$1),--(Sheet3!$A$2:$A
$6=Sheet4!$A2),Sheet3!$C$2:$C$6)