Using a defined name list as criteria in SUMIFS

[scecchin]

Hi

I have a table, (tbl1), with columns "trans" and "amt" (simple example). I have a separate table, (tbl2), of possible transactions that can be used in the "trans" column of (tbl1). (tbl2) has only 1 column.

From (tbl2), I created a defined name, (lst1), referring to the single column of (tbl2). Creating the list, (lst1), this way allows the list to automatically expand if I add more possible transactions to (tbl2). (lst1) is used as a validation list for the "trans" column of (tbl1). For the purposes of my question, let's say there are 4 transactions in the list; Buy, Sell, Contribute, Withdraw.

Hopefully this is clear so far.

I want to sum values from (tbl1) for only certain values of "trans". I can do this. The formula is below:

=SUM(SUMIFS(tbl1[amt],tbl1[trans],{"Buy","Withdraw"}))

This formula works fine and produces the correct result.

What I would like to do is replace {"Buy","Withdraw"} with the named list (lst1). Now I recognize in my example that all transactions in the list would be considered, however, if I can get this to work, I would create sub lists, (lst1a), (lst1b), etc., to get the correct values, but for now I cannot seem to figure out how to have a defined name list work at all.

Thanks for your help.
Sergio

 

[Somendra Misra]

@scecchin

If you define a named range say list with values {"Buy";"Withdraw"} and use in your formula in criteria of SUMIF it is working.

What eaxctly do you need? Consider uploading a sample file with input & output.

Regards,

 

[scecchin]

Hi

I have created a small example file of the data above. The formula is there once as it works and once it does not, for each of two lists

Thanks
Sergio

 

[Somendra Misra]

@scecchin

After entering your formula presee Ctrl+Shift+Enter to excute it and not just enter key. See if this bring you expected result.

Regards,

 

[Colin Legg]

Change the SUM() to SUMPRODUCT() since the named range needs to be dereferenced into an array.

eg.

=SUMPRODUCT(SUMIFS(tbl1[amt],tbl1[trans],lst1))



*untested

Also note that you could use SUMIF instead of SUMIFS in this particular formula since there is only 1 criteria which is an array.

 

[scecchin]

Both methods; Ctrl+Shift+Enter to create an array formula of the original attempt, and using SUMPRODUCT instead of SUM, worked.

Thank you for your help.
Sergio