return a value based on multiple table references

  • Hi All,


    Hope you can help me. i've been trying to write a formula to return a row sum based on 2 criteria but keep returning incorrect answers or #value.


    i have a duplicate table on another worksheet which is editable and compared against my master table on another worksheet.


    here is what i thought would work.


    =SUMIFS(Table2[data area]:[data area]",table2[column1],[@cell],table2[column3],table1[@cell]


    I've tried index match but still relatively new to that and did return various values but nothing that was close. I figure index match will work but not sure how to correct it.


    As well as numbers my table does contain some text and blank cells.


    Regards

  • Attached is my workbook problem.


    Return a sum of the row if the cells in column A&C in tab A = those in a&C in tab b


    Tab b will contain text and blank cells so they need to be ignored


    Whilst the values will equal the same initially i will be overwriting the sum data in tab b with text, therefore the value will change and i want to report that value to the corresponding row based on the match described above.


    i'm guessing an index/match but tried various and cannot get it to work.