sum of vlookup using array formula

I have such table of payments:

account | amount
--------+-------
101   | 3
101   | 5
102   | 7
103   | 9

I named the range of this table "payments". Its first column I called "accounts". Its second column I called "amounts".

I also have another table which assigns accounts to groups:

account | group
--------+-------
101   | 1
102   | 1
103   | 2

I named the range of this table "groups".

Now I want to sum all payments of accounts of group 1 using only one formula, without using any additional columns. I'm trying to do that using such array formula:

sum(if(vlookup(accounts,groups,2,false)=1,amounts,0))

I also tried such array formula:

sumif(if(vlookup(accounts,groups,2,false)=1,amounts,0))

These formulas do not work. I think I know why - it seems that vlookup function does not return array when used in one cell array formula.

So, how can I calculate it using one formula?

migrated from stackoverflow.comFeb 26 '13 at 4:15

This question came from our site for professional and enthusiast programmers.

It seems to me that you can achieve what you are looking for using the following formula:

=SUMPRODUCT(B1:B4*(LOOKUP(A1:A4,C1:C3,D1:D3)=1))

The =1 refers to the group number of which you are looking for the accounts. Note that this is a regular formula, not an array formula.

I have used regular cell references as opposed to named regions, because I think that makes it easier to relate the formula to the XLS sheet.

The following screenshot shows where the different values are and includes a slightly more generic formula that you can use to do the same calculation for different groups by dragging the lower-right corner of cell F1. If you want to stick to using names, you have to introduce to different names for the columns with accounts. the formula would look like this:

=SUMPRODUCT(Payments*(LOOKUP(Accounts1,Accounts2,Groups)=1))

For the sake of completeness, check out How to use the LOOKUP function in Excel for the conditions under which you can use LOOKUP.

• Using LOOKUP is a valid approach, Reinier, but there are some possible downsides - firstly for LOOKUP to work correctly the "lookup range" [C1:C3] must be sorted ascending (as per example but I don't know if that can be guaranteed) and secondly if any of the accounts1 codes don't appear in the accounts2 list then you would either get an error or a "false match" - my approach doesn't have those potential drawbacks – barry houdini Jan 30 '13 at 9:50
• @barryhoudini For the problem statement, LOOKUP is the right function. And on the upside, don't you think the resulting formula is more elegant and better maintainable than, for example, yours ;-)? But from an educational standpoint, you are correct, I should have mentioned that. I extended the answer. – Reinier Torenbeek Jan 30 '13 at 17:36
• @user983447 Glad to help. Check out the link I added, or barry houdini's comment, for conditions under which this approach using LOOKUP works. – Reinier Torenbeek Jan 30 '13 at 17:42
• more elegant and better maintainable? Yes, definitely, good answer! Perhaps I was trying to cope with non-existent issues – barry houdini Jan 30 '13 at 17:43

Yes, you are correct, VLOOKUP won't return an array so you need another approach. Assume that first column of groups is called accounts2 and second col is numbers then try this array formula,

=SUM(IF(ISNUMBER(MATCH(accounts,IF(numbers=1,accounts2),0)),amounts))

confirm with CTRL+SHIFT+ENTER

• As far as I can see, it does not work. – user983447 Jan 30 '13 at 16:11
• did you use CTRL+SHIFT+ENTER? If you use that key combination you will get { and } around the formula and hopefully the correct result too – barry houdini Jan 30 '13 at 16:33