Search code examples
excel-formuladynamic-arraysvalueerrorsumifs

excel formula - getting sumifs to work inside a let function

SumifsProblem_Clients

SumifsProblem_Returns

Hi! I'm trying to create a formula in excel that does 2 things:

  1. Join two tables into one. - I'm using HSTACK function to do so.
  2. Add a SUMIFS column in the end of the Jointed table.

I'm using the LET function - in order to "connect all the pieces of the puzzle" Now, It's important that the arguments inside of the SUMIFS function will NOT be referenced directly, but will instead be derived from the Jointed table I created.

However, I ran into a problem. When giving the first 2 arguments in SUMIFS as derived columns/arrays from the Jointed table - I receive a VALUE error in the SUMIFS column. (it's important to note : the third argument in the SUMIFS function, is able to receive a derived array, and does not require to be referenced directly)

From searching online, it seems that for some reason the SUMIFS (in the first 2 arguments) wants to receive a different kind of DATA, it needs an array and not a Range (although - I'm not sure that is the problem - just a speculation from searching online)

Can anyone help me?

I couldn't add the excel file, but I added pics of the situation. the SumifsProblem_Jointed_V1 pic is the one where everything seems to be working - but please note that this Version uses direct references in the SUMIFS function (in the first 2 arguments) - so it's no good.

in the SumifsProblem_Jointed_V2 pic you can see the problem.

here is the code for V1:

LET(Clients_Table,Clients!A2:C201,
Returns_Table,Returns!A2:B51,
Jointed_Table,HSTACK(Clients_Table,Returns_Table),
Client_ID_List,FILTER(CHOOSECOLS(Jointed_Table,1),ISNUMBER(CHOOSECOLS(Jointed_Table,1))),
Returns_ID_List,FILTER(CHOOSECOLS(Jointed_Table,4),ISNUMBER(CHOOSECOLS(Jointed_Table,4))),
Returns_List,FILTER(CHOOSECOLS(Jointed_Table,5),ISNUMBER(CHOOSECOLS(Jointed_Table,5))),
HSTACK(Jointed_Table,SUMIFS(Returns!B:B,Returns!A:A,Client_ID_List)))

SumifsProblem_Jointed_V1

here is the code for V2:

LET(Clients_Table,Clients!A2:C201,
Returns_Table,Returns!A2:B51,
Jointed_Table,HSTACK(Clients_Table,Returns_Table),
Client_ID_List,FILTER(CHOOSECOLS(Jointed_Table,1),ISNUMBER(CHOOSECOLS(Jointed_Table,1))),
Returns_ID_List,FILTER(IFNA(CHOOSECOLS(Jointed_Table,4),0),ISNUMBER(IFNA(CHOOSECOLS(Jointed_Table,4),0))),
Returns_List,FILTER(IFNA(CHOOSECOLS(Jointed_Table,5),0),ISNUMBER(IFNA(CHOOSECOLS(Jointed_Table,5),0))),
HSTACK(Jointed_Table,SUMIFS(Returns_List,Returns_ID_List,Client_ID_List)))

SumifsProblem_Jointed_V2

Please note that I don't wish to change the SUMIFS function to an XLOOKUP function. switching it will make the function work, but I will miss a big part of the SUMIFS ability to sum all IDs that are the same in the 'return_client_ID' column. if I use XLOOKUP it will only take the first return value and not all of them. That being said - if you have a way to get to the same results as the SUMIFS function with XLOOKUP or any other function - they will be welcome!

A VERY BIG THANKS FOR ANYONE THAT TRIES TO HELP!!

I thought maybe the problem has something to do with N/As that are received when connecting both tables - because the Returns table is much smaller. so i added IFNAs where they maybe needed and switched the N/As to zeros. but that didn't help..

Solution

  • SUMIFS requires a Range input for bot the SUM and Check Range. From the moment you stack ranges - and with HSTACK they're stored as an Array, not a Range - SUMIFS can no longer see it as a range.

    Therefore we need to bypass this limitation and we can do this with MMULT:

    MMULT(--(TOROW(Returns_ID_List)=Client_ID_List),Returns_List)

    This would make it

    =LET(Clients_Table,Clients!A2:C29,
Returns_Table,Returns!A2:B29,
Jointed_Table,HSTACK(Clients_Table,Returns_Table),
Client_ID_List,FILTER(CHOOSECOLS(Jointed_Table,1),ISNUMBER(CHOOSECOLS(Jointed_Table,1))),
Returns_ID_List,FILTER(CHOOSECOLS(Jointed_Table,4),ISNUMBER(CHOOSECOLS(Jointed_Table,4))),
Returns_List,FILTER(CHOOSECOLS(Jointed_Table,5),ISNUMBER(CHOOSECOLS(Jointed_Table,5))),
HSTACK(Jointed_Table,MMULT(--(TOROW(Returns_ID_List)=Client_ID_List),Returns_List)))

    But I think what you could use instead is the following:

    =LET(clients,FILTER(Clients!A:C,ISNUMBER(Clients!A:A)),
     returns,FILTER(Returns!A:B,ISNUMBER(Returns!A:A)),
HSTACK(clients,
       MMULT(--(TOROW(INDEX(returns,,1))=INDEX(clients,,1)),
             INDEX(returns,,2))))

    This will return only the clients data and the sum of the client's returns.