Total sum of the cells from multiple rows and 2 sheets

Hello.
I'm working with quite a large material and would appreciate your help a lot.
The information I need is in two sheets (which is not a real problem) but I've tried a lot of different combinations of functions and still can't get it done correctly. In first column there is an ID-number which is same for each person in both sheets. Each ID may have varying number of transactions (each in its own row). I would like to calculate the total values (not the number of the cells meeting the criteria) of each ID from both worksheets into a single cell. Is there any way getting it done?

You can put this formula in D2 of Sheet1:

=SUMIF(Taulukko1[[#All],[id]],Taulukko1[[#This Row],[id]],Taulukko1[[#All],[value]]) + SUMIF(Taulukko2[[#All],[id]],Taulukko1[[#This Row],[id]],Taulukko2[[#All],[value]])

and it should automatically fill down. You may need to change the commas ( , ) to semicolons ( ; ), depending on your regional settings.

Hope this helps.

Pete

Thank you for the help! I was able to get the formula work now though it required me to switch the language to english. :D
However, for further analysis I would need only one cell for each id-number as it now calculates the value for each row. Therefore there is multiple values if there is more than one transaction for that particular id. Any solutions for this?

Use this formula instead:

=IF(COUNTIF(A$2:A2,A2)=1,SUMIF(Taulukko1[[#All],[id]],Taulukko1[[#This Row],[id]],Taulukko1[[#All],[value]]) + SUMIF(Taulukko2[[#All],[id]],Taulukko1[[#This Row],[id]],Taulukko2[[#All],[value]]),"")

(changes in red). It will show the totals only on the first row of any ID.

Hope this helps.

Pete

Thanks, I owe you a lot. You saved me from so much more frustration.

You're welcome, and thanks for the rep.

You know where to come if you have any other problems.

Pete