4

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?

3 Answers 3

2

Almost 7 years later, I bring a VLOOKUP() solution with a single array formula :)

https://stackoverflow.com/questions/47187863/can-excels-index-function-return-array/59311618#59311618

A formula with entire columns would require a IFERROR(VLOOKUP();0) construct in order to deal with the headings, otherwise the SUM will return a #VALUE error.

Excel screenshot

    A         B       C   D         E       F   G       H
  +—————————+———————+———+—————————+———————+———+———————+————————————————
1 | account | group |   | account | group |   | group | total amount
  |---------+-------+---+---------+-------+---+-------+----------------
2 | 101     | 1     |   | 101     | 1     |   | 1     | {FORMULA HERE}
3 | 102     | 1     |   | 102     | 1     |   |       | 
4 | 103     | 2     |   | 103     | 2     |   |       | 

{FORMULA HERE}

en-us

=SUM(IF(IFERROR(VLOOKUP(N(IF({1},$A:$A)),$D:$E,2,0),0)=$G$2,$B:$B,0))

fr-fr

=SOMME(SI(SIERREUR(RECHERCHEV(N(SI({1};$A:$A));$D:$E;2;0);0)=$G$2;$B:$B;0))

Validate the array formula with CTRL + SHIFT + ENTER

3

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.

Screenshot showing values and more generic function

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.

4
  • 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 Jan 30, 2013 at 9:50
  • 1
    @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. Jan 30, 2013 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. Jan 30, 2013 at 17:42
  • more elegant and better maintainable? Yes, definitely, good answer! Perhaps I was trying to cope with non-existent issues Jan 30, 2013 at 17:43
2

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

2
  • As far as I can see, it does not work.
    – user983447
    Jan 30, 2013 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 Jan 30, 2013 at 16:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .