V-lookup and summing values if more than 1 matches criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to investigate a way in which I can get V-Lookup to sum the
values and bring back the total figure when more than one cell matches the
search criteria. E.g. if I am getting it to search for number 46 and I want
it to bring back the next column, and there are two 46 matches, and each of
the columns I want to bring back having a number in there, I want it to sum
the two numbers and bring back the total figure.
 
one way looking for 46 in col E and numbers in columns H:I
=SUM(INDIRECT("H"&MATCH(46,E:E)&":I"&MATCH(46,E:E)))
 
Try the SUMIF function rather than vlookup; the format is roughly:
=sumif(Range to search, What to search for, Range to add upon matching). In
your case something like =sumif(B:B,46,C:C).
 
Take a look at =sumif() in excel's help:

=sumif(sheet2!A:A,A1,sheet2!B:B)
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

With F1 value, look at A, if match or partial match return B 5
VLOOKUP and SUM 5
SUM - IF - AND 7
Time Sum in Excel 1
Add Values that Match 2 Criteria Q 1
V Lookup and Multiple Values 2
Multiple lookups and SUM function 4
Match with 3 criteria 3

Back
Top