Attempting to create a pivot-like report using a formula

Question

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)

Answer 1

First convert months to real date so that pivotby automatically sort them. And you can remove totals by declaring total depth by 0. Try-

=PIVOTBY(B2.:.B500,DATEVALUE("01-"&A2.:.A500&"-2025"),B2.:.B500,COUNTA,0,0,,0)

enter image description here

Note: The cells containing months in result area, you have to format those as MMM to show Jan, Feb ..... Otherwise it will show date serial as result.

• Spectral Instance: This does require that the range E2:I2 have the custom number format "mmm" since the formula as is returns date serials rather than the month names
• Harun24hr: Yes, you are correct. I forgot to mention it in post. Thank you for good catch.

Answer 2

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))

• Harun24hr: This is MONTH(α&0) a clever trick to sort months.

Answer 3

You could try and retain 'Total' within GROUPBY():

=CHOOSECOLS(PIVOTBY(B.:.B,A.:.A,B.:.B,ROWS,1,0),1,DROP(XMATCH(UNIQUE(A.:.A),SORT(UNIQUE(A.:.A)))+1,1),-1)

Answer 4

Not really different, then posted, but a different way of getting to the numerical and text presentation of the month:

=LET(s,B2:B27,m,--(A2:A27&-1),DROP(PIVOTBY(s,TEXT(m,REPT("m",{1,3})),s,ROWS,,0,,0),1))

The trick is in TEXT(m,REPT("m",{1,3}))

REPT("m",{1,3}) results in {"m","mmm"} (which would've been shorter, but less cool 😎) and creates one column of m converted to it's numerical value and a second column of m converted to it's text presentation. Since the numerical comes first, this will be presented as the first header of PIVOTBY and will also be used as a sort order. Removing the first row keeps the correct sort order preserved.

Answer 5

Here's one approach Screenshot illustrating effect of suggested formula

=LET(pvt,PIVOTBY(B2:B27,A2:A27,B2:B27,LAMBDA(x,ROWS(x)),,0,,0),sort,MATCH({"","Jan","Feb","Mar","Apr","May"},TAKE(pvt,1)),CHOOSECOLS(pvt,sort))

Answer 6

Without using LAMBDA
Months in the idx step is a sorted list of the months of the year. (eg {"Jan","Feb","Mar", ... "Dec"} ). It can be either a named range, or hard-coded.

=LET(
 p, PIVOTBY(
 myTable[Status],
 myTable[Month],
 myTable[Status],
 COUNTA,
 0,
 0,
 ,
 0
 ),
 hdrs, DROP(TAKE(p, 1), , 1),
 idx, 1 + XMATCH(Months, hdrs),
 idx_2, FILTER(idx, ISNUMBER(idx)),
 HSTACK(TAKE(p, , 1), CHOOSECOLS(p, idx_2))
)

enter image description here