criterium range broadcasting along sum range in `SUMIF`

Question

Can Google spreadsheets broadcast rows or columns in their functions, in particular in SUMIF, if different range dimensions are used for two arguments?

For example, I was hoping SUMIF(B1:F1, "Offices", B2:F4) would return 56+23+23, because the 1x5 range B1:F1 would be repeated in the row dimension to match the 3x5 range B2:F4. PS: this repeating is called dimension broadcasting. Unfortunately, this doesn't work, SUMIF just ignores the 2 rows it has no criteria for and returns 56.

     A       B            D        D     E          F
1    Month   Maintenance  Offices  Cars  Employees  Cars 
2    Jan     23           56       43    23         56
3    Feb     12           23       67    43         21
4    March   44           23       45    56         45

Question: Can I specify a criterium in SUMIF where the column or row stays fixed, as is possible with conditional formatting? Differently put, how can one specify a criterium in SUMIF such that the criterium range is broadcasted?

Why DSUM does not work: Notice that SUMIF(B1:F4,"Offices",B1:F4) could do the trick, only that it would fail here since the B1:F4 isn't a proper database since there are two columns named "Car". Moreover, DSUM requires the column headers to be adjacent to their data, while I would imagine I'd want to place the total sums between the header and the data. That being said, I also want to learn an imho powerful concept, not the DSUM function.

Conditional formatting Google spreadsheet does offer broadcasting in formatting, for example, if I format the range A1:F4 on the condition =A$1="Cars", then the two "Cars" columns would be formatted, even though they are columns D and F.

Comparison with numpy, which does broadcasting numpy, Which can be used as a spreadsheet library when programming Python, does something called (dimension) broadcasting. Consider an array (read spreadsheet) a of 1 row and 3 columns and another array b of 3 rows and 3 columns. We could then ask numpy to multiply both element- (read cell-) wise, and it will repeat the single row of a three times to match the dimension of b:

import numpy

a = numpy.array([
    [0, 1, 2]
])

b = numpy.array([
    [0, 1, 2],
    [3, 4, 5],
    [7, 8, 9],
])

a * b

Outcome, notice that the entire first column is multiplied with 0, the entire second with 1 and the entire third with 2:

numpy.array([
    [0, 1, 4],
    [0, 4, 10],
    [0, 8, 18],
])

@player0 The question is whether there is some syntax like REPEATROWS(A1:F1) which makes A1:F1 act as if it is a n x 6 range instead of a 1 x 6 range depending on the number of rows of a second argument. This principle is called broadcasting, where one dimension (e.g. rows or columns) is repeated to match the same dimension of a different argument, and is a very common feature of libraries dealing with (numerical) tensors (or tables). — Herbert
there is a way how to deal with two arrays of different sizes when it's not allowed to have different sizes, using REPT just to match the bigger array so it would pass in formula that requires same sized arrays (like filter for example) - stackoverflow.com/a/54795616/5632629 — player0

player0 player0 · Accepted Answer · 2019-03-06T08:51:06

1

votes

=SUMPRODUCT(QUERY(TRANSPOSE(A1:F10), "where Col1 = 'Offices'"))

criterium range broadcasting along sum range in `SUMIF`

1 Answers