help with formulas please

[keasha]

Hello there
hoping someone can help me with the formulas for this please. i have attached a workbook for you.

I have a data tab which has columns for Dept, Activity, Position Status, FTE and Gross S&W one column for each

the output tab, i wish to split out both the Dept and Activity in the rows then by position status FTE, Gross Wages.

hope this makes sense, and thankyou very much to anyone who can help!

 

[bluesky63]

Use Index & Match with multiple criteria
=IFERROR(INDEX(data!$H$4:$H$7,MATCH(1,($B4=data!$E$4:$E$7)*(C$3=data!$G$4:$G$7),0)),0)

As your mock up is not clear what you want to sum for, I will assume that first table to sum for FTE

Note that the table headers in output tab MUST BE the same as Position Status header values, so that it can be match and return the values for summing

Rgds

 

[keasha]

Hi , thankyou, yes understand. can you have a look if you dont mind at what i have done for columns D&F in the table - its not returning anything not sure why- sorry

 

[bosco_yip]

Try.........

[C4] =SUMIFS(IF(RIGHT(C$3,3)="FTE",data!$H:$H,data!$J:$J),data!$E:$E,$B4,IF(RIGHT(C$3,3)="FTE",data!$G:$G,data!$I:$I),C$3)

[C12] =SUMIFS(IF(RIGHT(C$11,3)="FTE",data!$H:$H,data!$J:$J),data!$F:$F,$B12,IF(RIGHT(C$11,3)="FTE",data!$G:$G,data!$I:$I),C$11)

All copied across and down

Please see attached file

Regards

 

[bluesky63]

Hi , thankyou, yes understand. can you have a look if you dont mind at what i have done for columns D&F in the table - its not returning anything not sure why- sorry

Typo Error, and also remember to press CTRL+SHIFT + ENTER after entering the formula

Yours : (D4) =IFERROR(INDEX(data!$J$4:JI$7,MATCH(1,($B4=data!$E$4:$E$7)*(D$3=data!$I$4:$I$7),0)),0)
Amended : =IFERROR(INDEX(data!$J$4:$J$7,MATCH(1,($B4=data!$E$4:$E$7)*(D$3=data!$I$4:$I$7),0)),0)

Column F formula is correct just need to press CTRL+SHIFT + ENTER, and copy down

 

[keasha]

wow, thankyou so very much to both of you !! i have more if you are ok to help me with them! i will post them up today. thankyou so so much