SUM multiple values (at different positions) over multiple sheets

0
votes

I am working on transferring an excel spreadsheet to Google sheets.

The spreadsheet is required to search through 10 sheets (labelled round1, round2, round3 and so on) looking for instances of a persons name and then sum together their totals.

The excel formula was: 

=SUMPRODUCT(SUMIF(INDIRECT("round"&{1,2,3,4,5,6,7,8,9,10}&"!A:A"),$C2,INDIRECT("round"&{1,2,3,4,5,6,7,8,9,10}&"!B:B")))

Following the convention SUMIF(range,condition,range) I've used the following in Google sheets:

=SUMIF({Round1!A2:A,Round2!A2:A,Round3!A2:A,Round4!A2:A,Round5!A2:A,Round6!A2:A,Round7!A2:A,Round8!A2:A,Round9!A2:A,Round10!A2:A},A2,{Round1!M2:M,Round2!M2:M,Round3!M2:M,Round4!M2:M,Round5!M2:M,Round6!M2:M,Round7!M2:M,Round8!M2:M,Round9!M2:M,Round10!M2:M})

But I am getting an error. I suspect I am missing something really simple?

3
arraysgoogle-sheetssumgoogle-sheets-formulasumifs
What error are you receiving? – WorkSmarter
Why don't you just use the excel formula? Please provide a link to the google sheet so we can copy / test it. – CodeCamper
The excel formula just reads the first table. – Keir Liddle
The google sheet is here: docs.google.com/spreadsheets/d/… – Keir Liddle

3 Answers

1
votes

try:

=SUMIF(
 {Round1!A2:A;Round2!A2:A;Round3!A2:A;Round4!A2:A;Round5!A2:A;Round6!A2:A;Round7!A2:A;Round8!A2:A;Round9!A2:A;Round10!A2:A}, 
 A2,
 {Round1!M2:M;Round2!M2:M;Round3!M2:M;Round4!M2:M;Round5!M2:M;Round6!M2:M;Round7!M2:M;Round8!M2:M;Round9!M2:M;Round10!M2:M})
1
votes

One of the best things about switching to Google Sheets is the QUERY() funciton.

Try this one long formula in A1 on a new tab...

=QUERY({Round1!A2:M;Round2!A2:M;Round3!A2:M;Round4!A2:M;Round5!A2:M;Round6!A2:M;Round7!A2:M;Round8!A2:M;Round9!A2:M;Round10!A2:M},"select Col1,SUM(Col13),SUM(Col5),SUM(Col10),SUM(Col11),SUM(Col12),Count(Col1),AVG(Col13),SUM(Col13)/10,SUM(Col6),SUM(Col7),SUM(Col8),SUM(Col9) where Col1<>'' group by Col1 order by SUM(Col13) desc label Col1'Name',SUM(Col13)'Total Time',SUM(Col5)'Time w/o bonus',SUM(Col10)'bonus/deductions',SUM(Col11)'Bonus',SUM(Col12)'deductions',Count(Col1)'Rounds played',AVG(Col13)'Average Time (rounds played)',SUM(Col13)/10'Average Time',SUM(Col6)'hits',SUM(Col7)'catches for',SUM(Col8)'catches against',SUM(Col9)'Clears'",0)

0
votes

Try This (the result will sum(Column A * Colum B) where column B= C2):

= sumproduct(
             sum(
                 arrayformula(
                   if (
                        {
                          'round1'!B:B;
                          'round2'!B:B;
                          'round3'!B:B;
                          'round4'!B:B;
                          'round5'!B:B;
                          'round6'!B:B;
                          'round7'!B:B
                        } = C2,
                        {
                          'round1'!A:A;
                          'round2'!A:A;
                          'round3'!A:A;
                          'round4'!A:A;
                          'round5'!A:A;
                          'round6'!A:A;
                          'round7'!A:A
                        } , 0
                     )
                   )
                ),
             sum(
                 arrayformula(
                   if (
                        {
                          'round1'!B:B;
                          'round2'!B:B;
                          'round3'!B:B;
                          'round4'!B:B;
                          'round5'!B:B;
                          'round6'!B:B;
                          'round7'!B:B
                       } = C2,
                        {
                          'round1'!B:B;
                          'round2'!B:B;
                          'round3'!B:B;
                          'round4'!B:B;
                          'round5'!B:B;
                          'round6'!B:B;
                          'round7'!B:B
                        } , 0
                      )
                   )
                 )
            )