Nice intro! Here's a (partial) workaround for the sorting issue... if you have the correct order of the shifts stored somewhere, say in a named range 'ShiftOrder', you can replace C2:C16 (the range of shifts) with HSTACK(XMATCH(C2:16,ShiftOrder),C2:16). That gives a range like this: 2 Afternoon 1 Morning 2 Afternoon 4 Overnight so when it groups on those two columns, it sorts on the first one first. You get the same output sorted in the order you want, but also with an extra column showing 1, 2, 3, 4. You can wrap the whole thing in DROP( , , 1) to drop the first column... but the total label at the bottom is in the first column, so that's not perfect either. It's certainly not as easy as rearranging a pivot table, but at least there's a sneaky workaround!
Hi Oz, welcome back! While your editor's video editing might not have the same fiery energy as yours, it's still great-just needs a touch of your signature fire. Regarding the issue with COUNTA in PIVOTBY, thanks for highlighting it. I didn't realize it counted blank cells that way. However, I think using the [filter_array] argument to filter out blanks could be a good solution.
Hey man, I would advise you to first turn your dataset into a table with headers so you can quickly use the references in your formula. Plus using the tab key to use the suggested function is a much faster way than doubleclick
COUNTA has always counted only NON-EMPTY cells. To be included in COUNTA the cell must contain some information even errors are included. The workaround is of course to fill empty cells with "" for example. If you want all names to be counted use the name column as the argument to ne counted. Then you don't have to fill all empty cells with "" or zero I havent tried it but I think you can control the order of the grouping by wrapping the first argument with CHOOSECOLS.
Yes! That issue with COUNTA has always been there. I didn’t know about it until a couple months ago. I was shocked because I’ve been in love with COUNTA for a long time. So, I had to let folks know so that they aren’t surprised like I was.
2:38 @@OzduSoleilDATAit's not all bad. In a table or range I have used COUNTA to distinguish between blank and empty cells. In many situatiins it would be very annoying to include empty cells in a calculatiin such as when a range stakes out extra cells at the bottom for "future expansion". I believe the best solution would be to add a "to include" optional arguement which contains the following options: include all blank and empty cells, include blank but not empty cells, include neither blank nor empty cells. Each argument would have its use cases.
Oz! I really really appreciate you, I like your character and your manner, the first thing pump to my mind when I remember you ( get red of these filter buttons 😂😂), Allah bless you and safe you🤍🤍
thank you for showing the new function!!! However it depends on the what version of 365. I'm using a personal version of Microsoft Office 365 and it doesn't have it. I hope Personal 365 will eventually get.
Nice intro!
Here's a (partial) workaround for the sorting issue... if you have the correct order of the shifts stored somewhere, say in a named range 'ShiftOrder', you can replace C2:C16 (the range of shifts) with HSTACK(XMATCH(C2:16,ShiftOrder),C2:16). That gives a range like this:
2 Afternoon
1 Morning
2 Afternoon
4 Overnight
so when it groups on those two columns, it sorts on the first one first. You get the same output sorted in the order you want, but also with an extra column showing 1, 2, 3, 4.
You can wrap the whole thing in DROP( , , 1) to drop the first column... but the total label at the bottom is in the first column, so that's not perfect either.
It's certainly not as easy as rearranging a pivot table, but at least there's a sneaky workaround!
Yes! Very sneaky workaround. Thanks for that!
Love your videos Oz, I always learn something new from every video I watch.
Thanks for sharing about PivotBy! That's a new find for me!!
Excellent!💪😁🌴🤙
🤙🏽🔥
Excel Is On The Fire Now🤩🤩🤩
Great video, thanks Oz
Good to see you brother I hope you post the valuable video in upcoming months
YES YES! So glad to see you, too. I'm getting back into motion.
Great to hear from u. Can u also teach us GROUPBY
Hi Oz, welcome back! While your editor's video editing might not have the same fiery energy as yours, it's still great-just needs a touch of your signature fire.
Regarding the issue with COUNTA in PIVOTBY, thanks for highlighting it. I didn't realize it counted blank cells that way. However, I think using the [filter_array] argument to filter out blanks could be a good solution.
Thanks so much for the support and comments. The editor and I will get there. 🙏🏼
Hey man, I would advise you to first turn your dataset into a table with headers so you can quickly use the references in your formula. Plus using the tab key to use the suggested function is a much faster way than doubleclick
COUNTA has always counted only NON-EMPTY cells. To be included in COUNTA the cell must contain some information even errors are included. The workaround is of course to fill empty cells with "" for example. If you want all names to be counted use the name column as the argument to ne counted. Then you don't have to fill all empty cells with "" or zero
I havent tried it but I think you can control the order of the grouping by wrapping the first argument with CHOOSECOLS.
Yes! That issue with COUNTA has always been there. I didn’t know about it until a couple months ago. I was shocked because I’ve been in love with COUNTA for a long time.
So, I had to let folks know so that they aren’t surprised like I was.
2:38 @@OzduSoleilDATAit's not all bad. In a table or range I have used COUNTA to distinguish between blank and empty cells. In many situatiins it would be very annoying to include empty cells in a calculatiin such as when a range stakes out extra cells at the bottom for "future expansion". I believe the best solution would be to add a "to include" optional arguement which contains the following options: include all blank and empty cells, include blank but not empty cells, include neither blank nor empty cells. Each argument would have its use cases.
@@richardhay645 “to include” would be awesome
Oz! I really really appreciate you, I like your character and your manner, the first thing pump to my mind when I remember you ( get red of these filter buttons 😂😂), Allah bless you and safe you🤍🤍
all good until the shadow of Freddy Krueger's hand (minute 11:36)....now I won't be able to sleep because of that and COUNTA's bug....Thank you Oz 😅🙈
🫣
☺️
thank you for sharing
YES YES!
thank you for showing the new function!!! However it depends on the what version of 365. I'm using a personal version of Microsoft Office 365 and it doesn't have it. I hope Personal 365 will eventually get.
A chocolate what?, I see in the caption now, teapot, good one
😃