What is the best way for refactoring excel formula

Question

Like I ask in title, what is the best way to write formula like this one below. I want sum from all sheets values for Germany (values are in B column)

=SUM(SUMPRODUCT(--(First!A2:A11="Germany"),(First!B2:B11)), SUMPRODUCT(--(Second!A2:A11="Germany"),(Second!B2:B11)), SUMPRODUCT(--(Third!A2:A11="Germany"),(Third!B2:B11)))

What is your opinion?

Answer 1

try:

=SUM(FILTER({First!B2:B11; Second!B2:B11}, 
            {First!A2:A11; Second!A2:A11}="Germany"))

Answer 2

In Google Sheets you can also use

=QUERY({L1:M9;O3:P7}, 
 "select sum(Col2) where Col1 ='xxx' label sum(Col2) 'Total' ",1)  

(Please adjust ranges to your needs)

How the formula works.

We create an array using curly brackets {} stacking the ranges one on top of the other using semi-columns ; even from other sheets like {L1:M9;O3:P7;First!A2:B11;Second!A2:B11}. One can add as many ranges as needed. Even open ranges.
The ,1 in the end of the formula shows that the very first row will be treated as a header row.

Please read more about the QUERY. function

Answer 3

One more option:

=SUMPRODUCT(({First!A2:A9;Second!A2:A9}="Germany")*{First!B2:B9;Second!B2:B9})