How can I use Array Formula with SUMIFs with multiple criteria

Question

I have been at this for a couple days now and could use some help on this matter… I am trying to sum up the DH and Miles values from the "Load List" sheet, for each Driver within two given dates using an Array Formula.

I have the following SUMIFS() for each cell in column "D" of “done Driver Data” sheet, and I get the correct result. But I would really like to convert it to an Array Formula.

For readability I have separated the formula by arguments.

=(SUMIFS('Load List'!M:M,
         'Load List'!G:G,A2,
         'Load List'!H:H,">="&$N$2,
         'Load List'!H:H,"<="&$O$2)
         +
  SUMIFS('Load List'!N:N,
         'Load List'!G:G,A2,
         'Load List'!H:H,">="&$N$2,
         'Load List'!H:H,"<="&$O$2))

Now, here is how far I've come...

=ArrayFormula({"Miles";sumifs('Load List'!M2:N,
                              'Load List'!G:G,A2,
                              'Load List'!H:H,">="&$N$1,
                              'Load List'!H:H,"<="&$O$1)})

Also tried this one which is adding the miles, but does not filter them...

={"Miles";ArrayFormula(sumif('Load List'!G2:G,A2:A,
                             'Load List'!M:M)+
                       sumif('Load List'!G2:G,A2:A,
                             'Load List'!N:N)+
                       sumif('Load List'!H2:H,N1,
                             'Load List'!N:N))}

It seems that SUMIFs is not supported unless regexmatch is used, which I also could not get to work. I've also tried using multiple SUMIF to get the same result. I read that Query is the way to go, but I have zero idea how to use query. I'd prefer to stick with Array if possible.

Here is a link to the sheet with the data. The Arrays are for C1:E1 (highlighted in blue)

https://docs.google.com/spreadsheets/d/1KC0yQYL50UOnIkvCslIbDM3PZVccx97uN1PbMf6Z52o/edit?usp=sharing

Answer 1

You can compose your SUMIFS/COUNTIFS criteria together into composite data until you have a list of permutations which can be filtered in one pass using SUMIF/COUNTIF.

For instance, if you need results where A="1" and B="2" and C="3"; make a new column where you concat your values together (A & B & C) and then run a SUMIF looking for "123" as your match against that column. When that is working, inline the column formula.

Here's an example: https://docs.google.com/spreadsheets/d/1XE76xZBMDMH2yU9BJ54fkBMHFsFVvPtIZJod8g8om7E

Answer 2

sum in arrayformula would be like this:

={"Revenue"; ARRAYFORMULA(IFNA(VLOOKUP(A2:A, 
 QUERY(QUERY('Load List'!G:O, 
 "select G,O
  where H >= date '"&TEXT(O1, "yyyy-mm-dd")&"' 
    and H <= date '"&TEXT(P1, "yyyy-mm-dd")&"'", 0), 
 "select Col1,sum(Col2) 
  group by Col1 
  label sum(Col2)''", 0), 2, 0)))}

Answer 3

This in B1 should help with the aggregation by Driver. Something similar can be done to aggregate by state:

=ARRAYFORMULA({"Count","DH","Miles","Revenue","$/mile","Weekly Avg.";IF(LEN(A2:A),VLOOKUP(A2:A,QUERY('Load List'!A:S,"select G,COUNT(G),SUM(M),SUM(N),SUM(O),AVG(R),SUM(O)/"&(Q1-P1)/7&" where H >= date '"&TEXT(P1,"yyyy-mm-dd")&"' and H<= date '"&TEXT(Q1,"yyyy-mm-dd")&"' group by G"),{2,3,4,5,6,7},0),)})

Answer 4

Note:

For Array Formulas to work, they need to be applied to matching-size arrays.

---

You will indeed have a matching number of cells in columns "DH" and "Miles" for each driver where the dates are within range (belonging to each row where conditions are met within your criteria_range). From these cells you would like the total sum of all, so you just want ONE result.

So something like the following would work:

=ArrayFormula(sum("Driver's-Condition-Matching_DH-Range"+"Driver's-Condition-Matching_Miles-Range"))

Problem is for that you need to find said ranges... You could use lookup functions or queries to do so.

But IMHO your first approach is simpler, flatter, more readable, better overall.

I posted this as an answer because it would have been too long of a comment. Hope it helps.