CODEWITHSUNDEEP
excelexcel-formulaoffice365
santhosha
santhosha

Reputation: 440

SUMIF with HSTACK

I have this table.My goal is to generate a single cell report using Array formuals.

Date Sales
3/1/2023 0
3/3/2023 2
5/1/2023 8
5/5/2023 11
5/9/2023 14
5/12/2023 14
5/16/2023 19
5/30/2023 0
5/31/2023 0
6/1/2023 2
6/1/2023 2
4/18/2023 3
4/28/2023 14
4/30/2023 14
5/1/2023 0

What I have tried so far is

=LET(d,TEXT(Table1[Date],"mmmm"),v,Table1[Values],ud,UNIQUE(TEXT(Table1[Date],"MMMm")),s,HSTACK(ud,{1;2;3;4}),s)

I want to use SUMIF inside HSTACK so as to obtain the result in the format as shown below.

Month Sales
March 02
April 31
May 52
June 4

Any pointers? please

Upvotes: 0

Views: 680

Answers (4)

Bam
Bam

Reputation: 585

Single-cell formula with only one array input needed:

=LET(
    array, A1:B16,
    dates, DROP(array, 1, -1),
    months, MONTH(dates),
    sales, DROP(array, 1, 1),
    headers, TAKE(array, 1),
    VSTACK(
        headers,
        HSTACK(
            UNIQUE(TEXT(SORT(dates), "mmmm")),
            MAP(
                UNIQUE(SORT(months)),
                LAMBDA(m, SUM(N(months = m) * sales))
            )
        )
    )
)

enter image description here

Upvotes: 0

P.b
P.b

Reputation: 11483

While you have an explanation and 2 posts showing good solutions, I wanted to show that SUMIFS, in this case, could still be used inside LET:

=LET(a,Table1[Date],
     m,UNIQUE(SORT(MONTH(a))),
     d,DATE(2023,m,1),
HSTACK(TEXT(m*28,"mmmm"),
       SUMIFS(Table1[Sales],a,">="&d,a,"<"&EDATE(d,1))))

enter image description here

Upvotes: 3

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27273

Only an alternative approach, though the post says about the usage of SUMIF() with HSTACK() while any IF()'s family dont work with arrays as its known the syntax always starts with range and not with arrays, which actually clarifies the most of it. However, this is an alternative approach without any LAMBDA() helper function.

enter image description here

• Formula used in cell D2

=LET(
     a, SORT(A2:B16),
     b, TEXT(TAKE(a,,1),"mmm"),
     c, UNIQUE(b),
     HSTACK(c,MMULT(N(c=TOROW(b)),DROP(a,,1))))

Or, if the header is needed then,

enter image description here

=LET(
     a, SORT(A2:B16),
     b, TEXT(TAKE(a,,1),"mmm"),
     c, UNIQUE(b),
     d,HSTACK(c,MMULT(N(c=TOROW(b)),DROP(a,,1))),
     VSTACK({"Month","Sales"},d))

One more thing, I would like to do instead of just getting sum of sales by months i would use months and years to have a proper comparison, if may in future the sales of previous years are added then it would not make any difference and mix up all the sales. However, it depends on ones requirement to show a report or presentation.

enter image description here

=LET(
     a, SORT(A2:B20),
     b, TEXT(TAKE(a,,1),"mmm-yy"),
     c, UNIQUE(b),
     d,HSTACK(c,MMULT(N(c=TOROW(b)),DROP(a,,1))),
     VSTACK({"Month","Sales"},d))

Upvotes: 3

JvdV
JvdV

Reputation: 75870

Please look past the Dutch date notations, but have a try with:

enter image description here

Formula in D1:

=LET(m,TEXT(SORT(A2:A16),"mmmm"),u,UNIQUE(m),HSTACK(u,MAP(u,LAMBDA(s,SUM(DROP(SORT(A2:B16),,1)*(m=s))))))

In G1 I put the simplest Pivot Table which is probably what you should be using here.

Upvotes: 4

Related Questions