Trying to remove a criteria from my dynamic array and it's not working the way I think it should.
Our take
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]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Summarise 2D Dynamic Array?Using Excel 365 on Windows 11 This should be simple, but I haven’t manged to find a way to do it (and Copilot has just generated a bunch of #REF! and #CALC! errors…) I’m trying to do some basic forecasting over multiple years, but using dynamic ranges so the start and end dates, and the number of categories forecast can update automatically. I can get to a dynamic forecast by month, but am drawing a blank when I try to summarise that by year. A simplified version of the sheet currently looks like this: https://preview.redd.it/li08zsp8w7rg1.png?width=584&format=png&auto=webp&s=2d61ac0c1d887bb3b18bb3f08a9542fbc72f439b Data entry in B4:E10 Months dynamic range (pink) calculated in C14 =EOMONTH(D2,SEQUENCE(1,F2,0,1)) and spills C14 to AL14, or wherever the last month is. Categories dynamic range (pale blue) calculated in B15 =FILTER(B4:.B10,B4:.B10<>"")) and spills B15 to B20 here. Years dynamic range (peach) is just a helper row =YEAR(C14#). It might not even be necessary other than visually. The Forecast dynamic range (green) is then =XLOOKUP($B15#,$B$4:.$B$10,$C$4:.$C$10,0)*(C14#>=XLOOKUP($B15#,$B$4:.$B$10,$D$4:.$D$10,0))*(C14#<=XLOOKUP($B15#,$B$4:.$B$10,$E$4:.$E$10,0)) All I want to do is put one formula in C25 to calculate the blue dynamic range total by year for each category. The years are a dynamic range (UNIQUE of C14 above) and the categories are just B15#. This it turns out is completely beyond me - I can calculate each row individually using SUMIFS quite easily, but cannot persuade it to calculate one SUMIF for each line using one formula Anyone got a good way to deal with this? Thanks. submitted by /u/sprainedmind [link] [comments]
- Sum formula for calculating month occurrences from dates works for everything except for January.I am attempting to automate the calculations of how often each month is recorded in another tab. The formula is working for all the other months, but as I am using this template to calculate values as I enter them, I arbitrarily set the limit to 2000, allowing me not to have to think about changing the formula as I enter new information. Unfortunately, it seems that the blank cells are being coded as "1" https://preview.redd.it/7zte9xn701vg1.png?width=972&format=png&auto=webp&s=ba131bec322f84ce55fa9fdbc35cedfcbeb12d9f I have tried going through the formula and found that all the blank cells are coding themselves as "true" only for the month of January (1). I attempted to add =IF(SHEET2!A3:A2000 = " ", 0, ... to the beginning of the formula, but it just gave me a spill error. I might be getting confused between excel vs. Google Sheets formulas. Thank you! submitted by /u/mentallylost14 [link] [comments]