excelexcel-formulaexcel-365

Attempting to create a pivot-like report using a formula

I am working with this data and attempting to create a pivot-like report using a formula, with months displayed as columns, status categories as rows, and the corresponding status counts under each month.

Month Status
Jan Y
Feb U
Mar P
Apr D
May Y
Jan U
Feb P
Mar D
Apr Y
May U
Jan P
Feb D
Mar Y
Apr U
May P
Jan D
Feb Y
Mar U
Apr P
May D
Jan Y
Feb U
Mar P
Apr D
May Y
Feb U

Here is my final result, you can see that months order is shuffeld which needs to be correct and I don't want that total. Is it possible to achive this using PivotBy or Groupby functions. THanks

Apr Feb Jan Mar May Total
D 1 1 2 1 1 6
P 1 2 1 1 1 6
U 1 2 1 1 2 7
Y 2 1 1 2 1 7

My attempt:

=PIVOTBY(B2:B27,TEXT(A2:A27,"mmm"),B2:B27,LAMBDA(x,ROWS(x)),,0)
Solution

  • Without using LAMBDA(), hard-coding the month, or named ranges, you could try this with PIVOTBY() to get the result you want:

    enter image description here

    =LET(
     α, A2:A27,
     δ, B2:B27,
     DROP(PIVOTBY(δ, HSTACK(MONTH(α&0), α), δ, ROWS, , 0, , 0), 1))

    Or this using TRIMRANGE() function reference operators:

    =LET(
     _a, A:.B,
     _b, TAKE(_a, , 1),
     _c, DROP(_a, , 1),
     DROP(PIVOTBY(_c, HSTACK(MONTH(_b&0), _b), _c, ROWS, 1, 0, , 0), 1))