Google-sheets – Sumifs with arrays of different size

google sheets

I want to sum the hours worked by a certain person in a spreadsheet with the following format:

| A  | B | C | D |  E | ... | S  |
| ID | 7 | 8 | 9 | 10 | ... | 24 |
| 123| 23| 32| 0 | 16 | ... | 34 |
| 323| 12| 4 | 5 | 16 | ... | 31 |
| 444| 32| 2 | 0 | 16 | ... |  9 |
| 123| 54| 3 | 0 | 16 | ... | 7  |

The third row is the minutes worked by that ID for that hour. How can I sum all the minutes worked between, say, hours 18 and 23? I've tried using SUMIFS but it throws the "arrays have different sizes" error.

I've spread the hours over columns for performance reasons (there are 5000+) rows in the sheet.

EDIT: the formula that throws the error is

=SUMIFS(G2:S3,A2:A,132)

The first argument includes more than one column which is what throws the standard sumif error. The formula is not the exact formula: I've simplified it, for the purpose of the question it's the same (hence SUMIFS despite using one criteria only).

Edit: Clarified table format

Best Answer

Formulas

Part 1

[Optional] Add the following label to T1: Row Total
Add the following formula to T2: 

=ArrayFormula(IF(LEN(A2:A),MMULT(N(G2:S),TRANSPOSE(SIGN(COLUMN(G1:S1)))),))

Part 2

In another sheet, add the following formula (assuming that the sheet with the data is named, Sheet1)

=QUERY(Sheet1!A:T,"Select A,SUM(T) group by A label SUM(T) 'Total'",1)

Explanation

Error

I've tried using SUMIFS but it throws the "arrays have different sizes" error.
...

=SUMIFS(G2:S3,A2:A,132)

The error occurs because G2:S3 and A2:A SUMIFS requires that both arguments be of same size but they aren't. Due to the data structure SUMIFS is not a good choice. More details at the end.

Formulas

The Part 1 formula was adapted from a spreadsheet by Adam Lusk (AD:AM), it makes sums across rows. This is a better alternative than to build something with SUMIFS, specially because it's a single formula rather than something that should be filled down and would require a lot of recalculation processing due to the large number of rows (5000+).

The Part 2 returns grouped sums by ID (one total for each ID), also this will have better performance than using something with SUMIFS.

As an alternative to part 2, you could use a pivot table.

Why SUMIF is not a good alternative.

By one side, the source data is in form of pivot table / cross-tab report (row and columns headers) but SUMIFS as well as other similar functions are intended to be used with data in the form a simple table (column headers only).

In order to be able to use SUMIFS, first we should "unpivot" or "normalize" the source data. An alternative is to split the formula in two parts. They are shown in the Formula section and explained in the above section.

Related Topic