Summing results of multiple VLookups?

  • Thread starter Thread starter Joan Edington
  • Start date Start date
J

Joan Edington

Hi,

I have a spreadsheet in which the first column is a budget code,
(columns 2 and 3 are irrelevant to the problem) and column 4 is the
budget that applies to the code. These codes are in the range 1 to
400, in sequence but not necessarily the complete 400 possibilities. I
have defined columns 1 to 4, all rows, as a range called Table.

These codes are logically grouped, sometimes with a range of numbers,
such as all the 80s, and sometimes a few random numbers.

What I need is a summary on sheet 2 with a sum of the budgets for each
code grouping. e.g. one cell may be the sum of all the budgets with
codes in the 80s.

VLookup works OK for a single code but I couldn't find any way of
returning multiple rows to sum without generating a morass of hidden
workings. I simply have too many codes to deal with this.

I then tried to create a VB function that could be used in the summary
cell. This function was meant to loop round VLookups for the desired
code range, adding the results to a variable which I then put into the
function result.

Unfortunately this returns #VALUE!.

I'm just a novice at VB and don't really know if what I'm trying is
possible or if I've just got it wrong. Any other methods would be
gratefully received as well!

Thanks,

Joan
 
If you'd care to post a small illustration of what you're dealing with,
a simple solution might be available.

Alan Beban
 
OK. I'll give it a go trying to keep it illustrative rather than the
whole, which is bviously too big.

Sheet 1

A B C D
Budgets
Code N/A N/A Budget
1 ? ? 1500
2 ? ? 200
5 ? ? 350
7 ? ? 750
10 ? ? 1200
11 ? ? 500
15 ? ? 600
17 ? ? 420
20 ? ? 800
30 ? ? 600
80 ? ? 750
81 ? ? 400
82 ? ? 350
90 ? ? 900
15 ? ? 250
97 ? ? 750
99 ? ? 400
110 ? ? 200
111 ? ? 1300

Sheet 2
Summary of Budget Code Groupings
A B
Group Sum of Group Budgets
Food(Codes 80-89) 1500
Services(Codes 1, 5 and 90-99) 3000
Wages(Codes 10-19 and 110) 2920

I hope this makes sense. As you can see there are a few problems:-

1. The list of codes is not a simple list and can fluctuate so simple
sums of known rows isn't good enough.

2. VLookups may or may not find all the codes in the group ranges so
possibly ISNA is also required.

3. Some selections are not simply ranges of numbers.

This is why I thought VB might be needed but I'm not sure how to use
it in Excel. I tried putting a VB formula in the summary cell but that
didn't work. Maybe that's not allowed. The help says VLookup can be
run from VB but isn't clear about from where.

I had hoped that I could set up arrays of codes for each summary cell
and call the function that did the VLookup, or something of the sort.

I would be very grateful for any help here.

Thanks again.

Joan
 
SumProduct should work for you

Try something like this:

For your first one
Food(Codes 80-89) 1500
Click to expand...
=SUMPRODUCT((A2:A20>=80)*(A2:A20<=89),D2:D20)
for your second:
Services(Codes 1, 5 and 90-99) 3000
Click to expand...
=SUMPRODUCT((A2:A20>=90)*(A2:A20<=99)+(A2:A20=1)+(A2:A20=5),D2:D20)

Use the * between and statements and the + sign between qualifiers
that are like or statements....

Random
 
Not that we need to be wedded to SumIf (SumProduct formulas and also Pivot
tables are good alternatives)...

For food:

=SUM(SUMIF(Sheet1!$A$3:$A$21,{80,81,82,83,84,85,86,87,88,89},Sheet1!$D$3:$D$
21))

For Services:

=SUM(SUMIF(Sheet1!$A$3:$A$21,{1,5,90,91,92,93,94,95,96,97,98,99},Sheet1!$D$3
:$D$21))

For Wages:

=SUM(SUMIF(Sheet1!$A$3:$A$21,{10,11,12,13,14,15,16,17,18,19,110},Sheet1!$D$3
:$D$21))

All of the above assume Codes to be true integers.
 
Thanks for the time but unfortunately it didn't do the trick since I
don't want to multiply anything.

Thanks anyway.

Joan
 
Many thanks Aladin. That works a treat. When my user said she was
using VLookups I was completely blinded to the easier and more obvious
(easy said with hindsight) options. I must stop making life harder for
myself.

Thanks again,

Joan.
 
You could add another column, in which you either type or lookup the
Group. For example:

Code Group N/A N/A Budget
1 Services ? ? 1500
2 ? ? 200
5 Services ? ? 350
7 ? ? 750
10 Wages ? ? 1200

Then, use SUMIF to calculate the totals, e.g.:
=SUMIF($B$2:$B$20,J4,$E$2:$E$20)
where J4 contains the name of a group.
 
Hi Joan,

I'd try using a pivot table, and group the items to report
in the totals you are looking for. Example:

Account code
80 Rent
81 Rates
82 Water
83 Elec

90 Salary
91 Pension
92 Commission
95 Bonus

If you group the data in the pivot table starting at 80,
in groups of 10. It will report two lines 80 - 89; and
90 - 99.

Hope this helps!

Amanda
 
It is not multiplication exactly. It uses arrays and multiplies the
1's and 0's with the sums... . Works fine actually. The problem is
that your initial totals are off. Do the math again and you will see
that you have an extra 15 in your list between 90 and 97. It threw me
off for a second as well, but they do work.

You may also want to add Sheet1! preface to each of the ranges, if you
are placing this on a different sheet.



Random
 
Back
Top