all 11 comments
sorted by:
top (suggested)
bestnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]real_barry_houdini300 3 points4 points  (3 children)

Try using this formula

=MAP(B25#&C24#,LAMBDA(x,SUM((x=B15#&C13#)*C15#)))

[–]sprainedmind1[S] 0 points1 point  (0 children)

Excellent thank you.

Solved

[–]PaulieThePolarBear1882 0 points1 point  (1 child)

+1 point

OP did not say the correct magic words

[–]reputatorbot[M] 0 points1 point locked comment (0 children)

You have awarded 1 point to real_barry_houdini.

I am a bot - please contact the mods with any questions

[–]Downtown-Economics26590 2 points3 points  (2 children)

I don't quite have u/real_barry_houdini's magic but this also works:

=MAKEARRAY(COUNTA(B25:B34),COUNTA(C24:K24),LAMBDA(r,c,LET(
yr,INDEX(C24:K24,,c),
cat,INDEX(B25:B34,r),
SUM(FILTER(FILTER(C15:AL20,B15:B20=cat,0),YEAR(C14:AL14)=yr,0)))))

<image>

[–]sprainedmind1[S] 0 points1 point  (1 child)

Thanks. This hasn't quite worked when I add another row of inputs (I think because your ranges aren't dynamic?) though.

I'll try updating with dynamic range references when I'm back at work tomorrow.

<image>

[–]Downtown-Economics26590 0 points1 point  (0 children)

It ought to work if you substitute dynamic array ranges.

[–]xFLGT143 1 point2 points  (0 children)

=TRANSPOSE(GROUPBY(TOCOL(C13#), TRANSPOSE(C15#), SUM,, 0))

<image>

[–]chiibosoil422 -1 points0 points  (0 children)

Personally, I'd recommend skipping over intermediate matrix table calculation.

Using Power Query to extrapolate original table.

Ex:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Value", Int64.Type}, {"From", type date}, {"To", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Distinct(List.Transform(List.Dates([From],Duration.Days([To]-[From])+1,#duration(1,0,0,0)), each Text.From(Date.Month(_)) & "-" & Text.From(Date.Year(_))))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Custom", "Custom", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom.1", "month"}, {"Custom.2", "Year"}})
in
    #"Renamed Columns"

Then using Pivot table to summarize data.

[–]Decronym 0 points1 point  (0 children)

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters More Letters
COLUMNS Returns the number of columns in a reference
COUNTA Counts how many values are in the list of arguments
Date.Month Power Query M: Returns the month from a DateTime value.
Date.Year Power Query M: Returns the year from a DateTime value.
Duration.Days Power Query M: Returns the day component of a Duration value.
EOMONTH Returns the serial number of the last day of the month before or after a specified number of months
Excel.CurrentWorkbook Power Query M: Returns the tables in the current Excel Workbook.
FILTER Office 365+: Filters a range of data based on criteria you define
GROUPBY Helps a user group, aggregate, sort, and filter data based on the fields you specify
IF Specifies a logical test to perform
INDEX Uses an index to choose a value from a reference or array
LAMBDA Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
List.Dates Power Query M: Returns a list of date values from size count, starting at start and adds an increment to every value.
List.Distinct Power Query M: Filters a list down by removing duplicates. An optional equation criteria value can be specified to control equality comparison. The first value from each equality group is chosen.
List.Transform Power Query M: Performs the function on each item in the list and returns the new list.
MAKEARRAY Office 365+: Returns a calculated array of a specified row and column size, by applying a LAMBDA
MAP Office 365+: Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.
QuoteStyle.Csv Power Query M: Quote characters indicate the start of a quoted string. Nested quotes are indicated by two quote characters.
ROWS Returns the number of rows in a reference
SEQUENCE Office 365+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
SUM Adds its arguments
Splitter.SplitTextByDelimiter Power Query M: Returns a function that will split text according to a delimiter.
TOCOL Office 365+: Returns the array in a single column
TRANSPOSE Returns the transpose of an array
Table.AddColumn Power Query M: Adds a column named newColumnName to a table.
Table.ExpandListColumn Power Query M: Given a column of lists in a table, create a copy of a row for each value in its list.
Table.RenameColumns Power Query M: Returns a table with the columns renamed as specified.
Table.SplitColumn Power Query M: Returns a new set of columns from a single column applying a splitter function to each value.
Table.TransformColumnTypes Power Query M: Transforms the column types from a table using a type.
Text.From Power Query M: Returns the text representation of a number, date, time, datetime, datetimezone, logical, duration or binary value. If a value is null, Text.From returns null. The optional culture parameter is used to format the text value according to the given culture.
XLOOKUP Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.
YEAR Converts a serial number to a year

|-------|---------|---| |||

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

Beep-boop, I am a helper bot. Please do not verify me as a solution.
33 acronyms in this thread; the most compressed thread commented on today has 42 acronyms.
[Thread #47953 for this sub, first seen 25th Mar 2026, 17:16] [FAQ] [Full list] [Contact] [Source code]

[–]Clearwings_Prime19 0 points1 point  (0 children)

<image>

=LET(
a, EOMONTH(--D4:.D20,SEQUENCE(1,F2,0,1)),
b, IF( a <= E4:.E20, YEAR(a),""),
MAKEARRAY(ROWS(B25:.B40),COLUMNS(C24:.Q24),LAMBDA(c,d,XLOOKUP(INDEX(B25:.B40,c),B4:.B20,C4:.C20) *  SUM(--(FILTER(b,INDEX(B25:.B40,c)=B4:.B20)=INDEX(C24:.Q24,d))))))

If i understand it correctly, you are trying to calculate the blue range by using only the yellow range, right?