Need help with an array formula using 2 tables.

[windrain]

Hi everyone,
I have a workbook that has many sheets and I want to combine totals from all the sheets onto one sheet. I have received alot of help with this workbook already but have run into another situation that I think should be simple but can't figure it out.

In the attached workbook on the Jan 2016 Tabl3 6 7 sheet, is Table 6 A14:H22,
Dbl CK Totals sheet is where the formula I am using is located K4 (highlighted).
K4 has the correct answer. K6 should be 546.52. Using table 6 and 7 (A23:G30) it should be (E16*F16)+(E18*G18)+(C25*F25).

Everything works fine with my formula IF the name in table 6 is only there one time. When I have to put it in 2 times (example BL) then the formula reads table 6 twice. I don't know why!

I am using the array (CTRL+SHIFT+ENTER) but still no luck. Here is the formula I am trying to use

{=SUMIF(Table6[COST OF CARE /Add-ons / Staff Trainings / Trainer],'Dbl Ck Totals'!$B6,Table6[Column4])*SUMIF(Table6[COST OF CARE /Add-ons / Staff Trainings / Trainer],'Dbl Ck Totals'!$B6,Table6[Jan])+SUMIF(Table7[ADD-ONS],'Dbl Ck Totals'!$B6,Table7[Column2])*SUMIF(Table7[ADD-ONS],'Dbl Ck Totals'!$B6,Table7[Jan])+SUMIF(Table6[COST OF CARE /Add-ons / Staff Trainings / Trainer],'Dbl Ck Totals'!$B6,Table6[Column4])*SUMIF(Table6[COST OF CARE /Add-ons / Staff Trainings / Trainer],'Dbl Ck Totals'!$B6,Table6[Jan2])+SUMIF(Table7[ADD-ONS],'Dbl Ck Totals'!$B6,Table7[Column2])*SUMIF(Table7[ADD-ONS],'Dbl Ck Totals'!$B6,Table7[Jan2])}

Also if I remove the BL from A18 then the formula works fine.

I am so confused!! Thank you everybody for your help with this.

[FDibbins]

This would probably be a LOT easier to trouble-shoot if you did not use such long names, and if you used regular ranges, not table format

[FDibbins]

This...

Please Login or Register  to view this content.

becomes this...

Please Login or Register  to view this content.

Niot much shorter but (for me anyway), MUCH easier to read.

Also, dont think that needs to be ARRAY entered

[windrain]

Thank you for the tip! However, it still does the math twice when the name has 2 lines. So with BL instead of E16*F16 + E18*G18 + C25*F25. It is doing E16*F16 + E18*G18 +E16*F16 + E18*G18 + C25*F25.
Thank you for any help!

[JeteMc]

This array entered formula works, yields the correct amount as stated in post #1, in K6:

Please Login or Register  to view this content.

Note: this assumes that a name would not appear twice in the range Jan 2016 Tble3 6 7!A24:A28.
Let us know if you have any questions.

[JeteMc]

Here's a better version of the array entered formula from post #5 that allows for names appearing more than once in Jan 2016 Tble3 6 7!A24:A28:

Please Login or Register  to view this content.

Let us know if you have any questions.

[windrain]

Thank you!! The formula from post #6 is exactly what I need! It works perfect.
Again Thank you so much for your time and help!!!

[JeteMc]

You're Welcome. Thank you for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.