ARRAYFORMULA sum with multiple conditions

Question

Let's say you have a list of employees by start date and department. You want to create an ARRAYFORMULA to calculate the total salary cost by month for each department like in this sheet. The months will go out for several years so want to use ARRAYFORMULA.

Looking for an ARRAYFORMULA for G2 that will handle the calculation for row 2 which is the Sales department for all months. That formula can then be replicated in G3 and G4 to handle the other departments.

You can't use SUMIFS or QUERY inside ARRAYFORMULA. Since you need to use less than with dates to calculate the total you can't use SUMIF with composite keys. I tried using a FILTER inside SUMIF's inside ARRAYFORMULA but doesn't look like you can use FILTER inside a SUMIF.

Any suggesting for creating an ARRAYFORMULA function to handle this that's still condense enough to manage?

query can't be used in an ARRAYFORMULA. Pivot tabls wont' address the situation and "if" doesn't work over ranges unless you have a thought that I'm not seeing? — jadent
Actually I can see OP's point now, because they want a cumulative total it's difficult to see how you could do it with an array formula. — Tom Sharpe
I can see a long way of doing it by matrix multiplying the pivot query result by a diagonal matrix but it would be quite a faff. — Tom Sharpe
@I'-'I Not sure what cell you're referring to since H9 isn't in the matrix. But I added what the matrix result should be as an example of the the final output. It's salary costs in each month so if the salary is 1 in June, and another hire starts in July with a salary of 1, July's total salary cost is going to be 2 — jadent

Tom Sharpe Tom Sharpe · Accepted Answer · 2018-07-22T14:24:04

Well as I said it is a lot of faffing about but you can do it by generating a diagonal matrix

1   1   1   1
0   1   1   1
0   0   1   1
0   0   0   1

and multiplying the results of a normal pivot query by it so you get the first column, sum of first two columns, first 3 etc.

=ArrayFormula(if(isnumber(query(A:D,"select B,sum(D) where B <> '' group by B pivot C",1)),
mmult(n(query(A:D,"select B,sum(D) where B <> '' group by B pivot C",1)),
if(column(indirect("A1:"&address(countunique(C:C),countunique(B:B))))>=row(indirect("A1:"&address(countunique(C:C),countunique(B:B)))),1,0)),
query(A:D,"select B,sum(D) where B <> '' group by B pivot C",1)
))

Whether this fulfills the 'condense enough to manage' criterion is another matter.

Alternative

If you can tolerate a separate formula in cols G, H, I etc., here's an alternative approach. I'm still going to use queries but the problem is if one of the departments isn't in range for a particular date there will be fewer categories in columns H, I etc. than the list in column G and they won't match up. The simple answer after much thought is to set out-of-range values to zero -before- entering the query so for G2 you have

=sort(unique(B2:B))

to get the department names into alphabetical order

then in H2

=ArrayFormula(query({$A:$C,$D:$D*($C:$C<=H1)},"select sum(Col4) where Col1 <>'' group by Col2 label sum(Col4) ''"))

and just pull it across into column I,J etc.

See if this works for you.

ARRAYFORMULA sum with multiple conditions

2 Answers