VLookup returning the sum of multiple values from one "code"

M

Matt

Here is my array:
Col A Col B
Row 1 M 1
Row 2 M 2
Row 3 M 3
Row 4 N 4
Row 5 N 5

Not sure if the title of my question is correct, but I think I need to use
the VLookup function...I need to achieve the following for a dataset:
Assuming that Column A has a set of some text or codes (in my example just
two--"M" and "N"), and Column B has a set of corresponding numbers, I need to
have a formula (in another worksheet), that totals all values from one code.
for example, to total all rows where the row code is "M", the answer to the
formula would be "6" (1+2+3), and all rows where the code "N" exists, that
answer would yield the number/value of "9" (4+5).

Hope this is clear, and thanks for any help!
 
E

exalan

Hi

You can use sumif function (assuming the data set is in Sheet 1):

In Sheet 2:

=sumif(Sheet1!A1:A5,"=M",Sheet1!B1:B5)

and do same fo "N"

hope this helps
 
P

Pete_UK

Try it this way:

=SUMIF(Sheet1!A:A,"M",Sheet1!B:B)

Or you could put the M into a cell (A1 on Sheet2), then it becomes:

=SUMIF(Sheet1!A:A,A1,Sheet1!B:B)

Hope this helps.

Pete
 
M

Matt

Great, thank you both!

Pete_UK said:
Try it this way:

=SUMIF(Sheet1!A:A,"M",Sheet1!B:B)

Or you could put the M into a cell (A1 on Sheet2), then it becomes:

=SUMIF(Sheet1!A:A,A1,Sheet1!B:B)

Hope this helps.

Pete
 

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

Top