Hard Question - Chatbot failed with multpile attempts

[pban]

Hello, I have a simple yet very hard challenge to realise given the limited excel capability.

For example, I am working with a table within sheet Table1!A:C. In another tab I have one dynamic range A1# and other dynamic range B1# taken from some other source. Both are different in row counts as they're dynamic, yet they are linked with information in Table1. Range A1# has matching values in column Table!A:A and range B1# has matching values in column Table!B:B.

What I want to find and sum, is the matching data (it is a number defining by how much of A there is in B, typical bill of materials example) in column Table1!C:C. There may be multiple output results, hence why xlookup is out, and filter with sumif is unable to perform a calculation with these # dynamic ranges.

Has anyone found an example or has a solution that works in this case?

A close result gives me =FILTER(Table1!B:C,ISNUMBER(MATCH(Table1!A:A;A1#,0))) where I get the matched column B:B and C:C in an array. However I have no idea how to then combine column B:B with input B1# to then match the values C:C as final value.

Any help would be much appreciated.

 

[JamesCanale]

I can't solve your problem directly, but you can get something like 'sumifs' to work using arrays instead of ranges (See spilling F2):

MrExcelPlayground23.xlsx
	A	B	C	D	E	F
1	Salespeople	Month	Sales		Salepeople	Total
2	James	Jan	5		James	64
3	Fred	Jan	43		Fred	62
4	Harry	Jan	5		Harry	81
5	James	Feb	5
6	Fred	Feb	6
7	Harry	Feb	7
8	James	Mar	45
9	Fred	Mar	4
10	Harry	Mar	3
11	James	Apr	2
12	Fred	Apr	5
13	Harry	Apr	64
14	James	May	7
15	Fred	May	4
16	Harry	May	2
Sheet17

Cell Formulas
Range	Formula
E2:E4	E2	=UNIQUE(Table10[Salespeople])
F2:F4	F2	=MMULT(--(TOROW(Table10[Salespeople])=E2#),Table10[Sales])
Dynamic array formulas.

 

[Fluff]

Hi & welcome to MrExcel.

Can you post some sample data, along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[pban]

Thanks for the replies, I managed to solve the issue using lambda and byrow with the let and sumproduct (also within Table1 I needed to neglect headers with a transpose to make sumproduct work).

=LET(categories,B1#,results,SUMPRODUCT((Table1!A2:A1000=TRANSPOSE(categories))*(ISNUMBER(MATCH(Table1!A2:A1000,A1#,0)))*Tabel1!C2:C1000),results)