Embed a list range into a sumifs formula

[edmundmckay]

Hello

I am using the below formula to generate TB table gathering the sum amount from a general ledger within a year end date range against each chart code (column A in image below). There are transaction codes which can be ignored within the GL and the codes in in columns I-N in the image below ("OBJS", "JADJS", "OCS", "DOBS", "DCLP", "DIRES") are to be included. I only know how to get the information using the below formula. Therefore having to to create 6 columns of result cells (columns R-W) and use 6 columns of criteria columns with the individual codes (columns I-N).

Please could someone tell me how to get rid of the columns and embed the list of the codes ("OBJS", "JADJS", "OCS", "DOBS", "DCLP", "DIRES") into the formula in order for me to only use one column to return the amount for the date range.

On the GL sheet column J is the amount column , column F is the chart code column, Column A is the account date, Column M is the transaction code column.

=SUMIFS('GL Trans at 29.08.2017'!$J:$J,'GL Trans at 29.08.2017'!$F:$F,$A10,'GL Trans at 29.08.2017'!$M:$M,$I10,'GL Trans at 29.08.2017'!$AJ:$AJ,">="&R$6,'GL Trans at 29.08.2017'!$AJ:$AJ,"<="&R$8)

kind regards

Edmund

 

[FormR]

Re: How to embed a list range into a sumifs formula

Hi, you can try like this:

=SUM(SUMIFS('GL Trans at 29.08.2017'!$J:$J,'GL Trans at 29.08.2017'!$F:$F,$A10,'GL Trans at 29.08.2017'!$M:$M,{"OBJS","JADJS","OCS","DOBS","DCLP","DIRES"},'GL Trans at 29.08.2017'!$AJ:$AJ,">="&R$6,'GL Trans at 29.08.2017'!$AJ:$AJ,"<="&R$8))

 

[edmundmckay]

Re: How to embed a list range into a sumifs formula

Hi FormR

Thank you for your prompt response. This is exactly what I was trying to get.

The list of codes are going to grow with future TBS.
Is there a way to embed a data list instead of me adding to the list of codes within the formula?
Ideally it would be great if I can just add to a list within the worksheet which the formula looks up against in the same function as the list you have just embedded to the formula.

Many thanks

Ed

 

[FormR]

Re: How to embed a list range into a sumifs formula

Ideally it would be great if I can just add to a list within the worksheet which the formula looks up against in the same function as the list you have just embedded to the formula.

Hi, you can try like this, obviously "list" needs to substituted to a range of cells that contains the codes or a named range that refers to the same:

=SUMPRODUCT(SUMIFS('GL Trans at 29.08.2017'!$J:$J,'GL Trans at 29.08.2017'!$F:$F,$A10,'GL Trans at 29.08.2017'!$M:$M,list,'GL Trans at 29.08.2017'!$AJ:$AJ,">="&R$6,'GL Trans at 29.08.2017'!$AJ:$AJ,"<="&R$8))

 

[edmundmckay]

Re: How to embed a list range into a sumifs formula

Hi FormR

Great this will work nicely. I appreciate your quick, helpful responses today.

Many thanks