Extract data(numerical) to match a total

  • Thread starter Thread starter SJS
  • Start date Start date
S

SJS

I am trying to extract values from a numerical data base for a specifie
subtotal. E.g two columns of database.

Col. A Col.B
xx 25
cc 100
bb 200
zz 300
ff 400
dd 500

Now I would like to extract only those data, sum of which in column "B
equals 525, which should be the first row and the last row. Of cours
it would be hard to figure this out on a large database. I am confiden
some of you experts might be able to help me. Thanks
 
I dont fully understand what your trying to do but heres two ideas.

use VLOOKUP(525,A:A,0) - although I always find VLOOKUP never does what I want
it to do (Im sure loads of you are laughing at my syntax now saying "I know
why")

also I think it only returns one value.

An alternative - but much more long-winded method is to use a combination of
MATCH, INDEX, and OFFSET.

INDEX(OFFSET(A1,0,0,10000,1),MATCH(525,OFFSET(B1,0,0,10000,1),0))

So if you put that in C1 and then autofilled down your data.

I ought to go through it because it almost certainly wont work without me
having seen your actual spreadsheet.

The first argument of index specifies an array to be looked in - in this case
its just a column. Selecting the column A:A rather than a set of cells
A1:A100000 (for example) sometimes doesnt work. So if you use an offset this
gets round it. The offset has two 0 values in it which means it doesnt offset.
The 10000 is an arbitarily big number - I dont think it matters what you put
here (come to think of it a small numbr might work just as well. 1 is the
width. It should then return the value in column A whenever it finds a value in
B which says 525 - which is what the match does. 525 is what it looks for - the
offset is the range its looking in and the 0 I forget.

Just as a footnote :- Im not sure if youre putting subtotals in :- in this case
you could play with whats being matched - for example look in the subtotals
column for ("*Total")

Jonathan B
 
Back
Top