CODEWITHSUNDEEP
google-sheetsarray-formulassumifs
Max Ambinder
Max Ambinder

Reputation: 1

SUMIF Array Formula with merged columns + greater than date and less than date

table screenshot

I need an array formula in L2 (column Total Orders) with results like on screenshot and that will

Is it possible to solve it with array formula?

I've tried but I don't know how to find any solution for this

=ArrayFormula(IF(G2:G="","",SUMIF(B2:B&C2:C&D2:D&A2:A&A2:A,G2:G&H2:H&I2:I&">="&J2:J&"<="&K2:K,E2:E)))

Google Sheets

Upvotes: 0

Views: 224

Answers (2)

Aresvik
Aresvik

Reputation: 4620

This formula will calculate the row-by-row Total Orders by the keys in columns G:I using the dates in columns J and K as filtering criteria:

=arrayformula( 
  if( 
    isblank(G2:G), 
    iferror(1/0), 
    mmult( 
      ( 
        transpose(B2:B & C2:C & D2:D) 
        = 
        G2:G & H2:H & I2:I 
      ) 
      * 
      ( 
        (J2:J <= transpose(A2:A)) 
        * 
        (transpose(A2:A) <= K2:K) 
      ),
      n(E2:E) 
    ) 
  ) 
)

The formula should go to cell L2.

See the new Solution sheet in the sample spreadsheet.

Upvotes: 1

doubleunary
doubleunary

Reputation: 18784

Try this query() formula:

=query( 
  A1:E, 
  "select B, C, D, min(A), max(A), sum(E) 
   where A is not null 
   group by B, C, D 
   order by min(A)
   label min(A) 'Start Date', 
         max(A) 'End Date', 
         sum(E) 'Total Orders' ", 
  1 
)

This is an array formula that will create the whole result table in one go. See your sample spreadsheet. The formula is in cell G9.

Upvotes: 0

Related Questions