different tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ankur - 31 Oct 2005 12:16 GMT

Table 1 is as below :
P Q R S
Basic 1 3 5 7
HRA 5 7 2 6

Table 2 is as below
April May June July
Dept P 2 3 4 5
Dept Q 11 12 14 15

I want to determine department wise line itme wiase cost. Please guide me.
Eg. Department cost of "P" for April will be Basic (2*1) & Basic will be
(2*5=10).

Kindly help

Reply to this Message

Domenic - 31 Oct 2005 13:56 GMT

Assumptions:

Table 1...

O2:O3 contains Basic and HRA

P1:S1 contains April, May, June, and July

P2:S3 contains your data

Table 2...

O7:O8 contains Dept P and Dept Q

P6:S6 contains April, May, June, and July

P7:S8 contains your data

O12 contains the department of interest, such as Dept P

P12 contains the month of interest, such as April

Q12 contains Basic or HRA

Formula:

=SUMIF($O$7:$O$8,O12,INDEX($P$7:$S$8,0,MATCH(P12,$P$6:$S$6,0)))*SUMIF($O$
2:$O$3,Q12,INDEX($P$2:$S$3,0,MATCH(P12,$P$1:$S$1,0)))

Hope this helps!

> Table 1 is as below :
> P Q R S
[quoted text clipped - 11 lines]
>
> Kindly help

Reply to this Message