Home |
Search |
Today's Posts |
#1
|
|||
|
|||
different tables
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 |
#2
|
|||
|
|||
different tables
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! In article , "Ankur" wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I keep file sizes small when using multiple pivot tables? | Excel Discussion (Misc queries) | |||
How to pull numbers from two tables with conditions? | Excel Worksheet Functions | |||
Macro for Pivot Tables | Excel Discussion (Misc queries) | |||
Autofiltering multiple tables on a spreadsheet | Excel Worksheet Functions | |||
How to paste Xcel tables legibly into Word . Help!! | Excel Discussion (Misc queries) |