Trying to remove a criteria from my dynamic array and it's not working the way I think it should.
I have a dynamic array formula that I've been using to get certain statistics that I wanted based on the month.
=DROP(GROUPBY(Fanfiction[[#All],[New/ReRead]],Fanfiction[[#All],[Words]],HSTACK(COUNT,SUM),0,1,,(Fanfiction[[#All],[Month]]="January")),1)
It's done exactly what I needed and displayed the way I wanted. As seen in the first image. However I wanted to adjust it to show the same statistics for the entire year and not just a single month. So I adjusted the formula to the following.
=DROP(GROUPBY(Fanfiction[[#All],[New/ReRead]],Fanfiction[[#All],[Words]],HSTACK(COUNT,SUM),1,1,),1)
Overall it works but now I've got this row of zeros between the main results and the Total row (second image) that didn't show up in the original iteration of the formula. I considered nesting a second DROP or using TAKE and simply adding a Total row but realized that would only work if the number of rows in the array never changed. Which they will.
I could replace it with pivot tables, and I have for the moment, but I feel like the formula should do what I want and I just can't figure out why it's not. Any help would be greatly appreciated because I've been banging my head on my desk for two days for what is probably a very simple fix.
[link] [comments]
Want to read more?
Check out the full article on the original site