Summarise 2D Dynamic Array?
Our take
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:
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.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Sum of a range based on 2 different criteria?Hello, I'm working on a document which is a holiday funds to keep track of individual person's remaining balance to pay. I want to use a formula to automatically calculate the sum of a range of cells which lie against a certain year under a person's name. Below as an example: https://preview.redd.it/1p19n1o8qrxg1.png?width=991&format=png&auto=webp&s=2e67c0337da92ae1feffe43fc2c7fbaba237d672 As above, I want to look up Name 1 in 1:1, look up Year 1 in A:A and then return the sum of those numbers in that Year 1 range to give me my final result. Is this possible? I've tried all sorts of SUM, SUMIF, SUMIFS, XLOOKUP and combinations but can't seem to get it to work. I also don't want to have to manually select the cells for year SUM as this is constantly updating and want it to be automated. Thank you! submitted by /u/celestialspace [link] [comments]
- Is it possible to rewrite this formula to not have to retype every xlookup?Example data and intended result For the data in C2:G12 I need to find the corresponding percentages of X,Y,Z for each of A:D and then add them all together for each year. Ideally I'd like to simplify this formula since in some cases I have up to 20 columns to retrieve the components for. I've tried =BYCOL(D2:G2,LAMBDA(x, XLOOKUP(x,$I$2:$I$5,$J$2:$L$5,,0))) but I receive a CALC error and I'm really not experienced with using LAMBDA in excel in this way. Unfortunately I cannot use VBA for this particular problem. submitted by /u/Caconym32 [link] [comments]
- Countifs function comparing the number of occurrences of a value within a date range without having to change the date for every cell?https://preview.redd.it/5wv7p6qq6dvg1.png?width=1523&format=png&auto=webp&s=8678f87180d0cf5c410d2f14fdaefc77ca4dc255 I am a self-taught excel newbie and am trying to help automate the calculation of the stats at my job (meaning I will likely be back with multiple more questions). For this problem, I am trying to get the data to automatically calculate as information is entered in another sheet to determine how often certain values (e.g. "Academic") appear within each month. For some reason, when I put the cell (H2) in place of the date, excel does not register it as a cell and will not complete the calculation. The only solution I have found is to have to individually type out each date range rather than referring to the cell that contains the date range. Further, is there a way to not have to include the year? in this job, we have a separate file for every school year meaning there will be no overlap of month causing issues that way. I am trying to find an easier way to do a year to year comparison without having to include helper columns with the beginning and end dates of each month per year. https://preview.redd.it/55l3ixa49dvg1.png?width=1140&format=png&auto=webp&s=3b222fe0266778a2645f72fed52b59efae4ed850 Thank you! submitted by /u/mentallylost14 [link] [comments]
- Is there a way to Sumifs up to a certain column with multiple criteria?Hi everyone, I’m looking for a formula to sum all of the columns starting from a particular column up to a particular column. How do I best do that? On the data tab, Row 1 I have the following columns: A1 = date B1 = serial number C1 = customer name D1 = date 1, with the data being $ E1 = date 2, with the data being $ F1 = date 3, with the data being $ In a separate tab I have the same thing mirroring the data tab, but without the $, for the calculation what I intend to do is on E2, I want to say if $A2 = E$ 1, then sum all of D up to E column on the data tab provided that the value on B column of the data tab match B2 of the calc tab, and C column of the data tab match C2 of the calc tab. For F2, it would be the same thing, but sum column D up to column F instead of column E. I hope it makes sense. Thanks for your help! submitted by /u/botng [link] [comments]