Sum current balance of loan type based on list of product codes

[JacobRH]

Hello All,


I know it can be done in a Pivot table but, I am trying to see if there is a way to sum the current balance for the loan type based on product codes without having to enter 17 criteria into a SUMIFS() or SUMPRODUCT()formula.


In Column A I have the product code for the loan, in column B is the current balance for the loan, ie...

[pre]

Column A  Column B
250       20368.89
190       1016210.34
290       988.28
251       312.82
171       298753.68
183       898567.35

[/pre]
The totals I am looking for (loan type) would be for Operating Line and Real Estate where the following product codes make up Operating lines (250,251,252,253,280,281,290,291) and Real Estate would include the following product codes (160,161,162,163,164,165,170,171,172,173,174,175,180,181,182,183,190).


I've tried making named ranges for Real_Estate and OL and use that as my criteria when evaluating column A but it only looks at the first n cells and returns #N/A for the remaining cells in the range.


Any help would be greatly appreciated.

 

[Luke M]

Array formula (confirm using Ctrl+Shift+Enter)

=SUM(IF(ISNUMBER(MATCH(A2:A100,Real_Estate,0)),B2:B100))


=SUM(IF(ISNUMBER(MATCH(A2:A100,OL,0)),B2:B100))


Another idea would be to have a helper column in your Data that does a LOOKUP to determine the loan type. The LOOKUP table would be a good idea if you have lots of different types of loans. Then you could do a SUMIF off of that.

 

[Haseeb A]

Hello Jacob,


One option is hard code the criteria with in {} in SUMIF & add by SUM


=SUM(SUMIF(A:A,{250,251,252,253,280,281,290,291},B:B))

=SUM(SUMIF(A:A,{160,161,162,163,164,165,170,171,172,173,174,175,180,181,182,183,190},B:B))


Or If you have already named range for product codes Real_Estate and OL, use it like


=SUMPRODUCT(SUMIF(A:A,Real_Estate,B:B))

=SUMPRODUCT(SUMIF(A:A,OL,B:B))


Second option would be more preferable. You can use dynamic range for product codes, so you can add/delete entries in product codes.

 

[JacobRH]

Thanks, Luke! I was missing the ISNUMBER() piece.

 

[JacobRH]

Haseeb,


Could I also use =SUMPRODUCT(IF(A:A,Real_Estate,B:B)) or is the SUMIF() necessary?


Thanks for your help.

 

[Luke M]

Or, going off of Haseeb's idea, this array formula will also work:

=SUM(SUMIF(A:A,OL,B:B))


that's got to be the first time I've ever had a SUMIF directly inside a SUM...

 

[Luke M]

In the SUMPRODUCT function, the SUMIF is necessary. Another formula, non-array

=SUMPRODUCT((ISNUMBER(MATCH(A2:A100,OL,0)))*(B2:B100))

 

[JacobRH]

Awesome! Thank you both for all your help.