Solved

# Summing some of the values in a table in Excel.

Posted on 2011-03-22
258 Views
Hello,

Suppose you have a table of values in an Excel (2007) spreadsheet which is 10 columns wide and many rows in length (say A3:J5000).  Furthermore, suppose that column B has only three possible entries:  D, C and W.  In other words, none of the entries in column B is unique.

Now suppose that for a specified range in the table, you want to use =SUM() to add-up all the values (but only the values) in column G which have a "D" in column B.  What formula would do that?

I am familiar with using =LOOKUP() to locate values in a list or table.  However, I believe that =LOOKUP() is only usable when all values in the lookup column are unique.  Is that correct?

If so, then here, I am looking for a function in which those values do not have to be unique.

Thanks
0

LVL 50

Accepted Solution

barry houdini earned 500 total points
ID: 35191567
Hello Steve

Sounds like you'd want SUMIF, so for the whole table

=SUMIF(B3:B5000,"D",G3:G5000)

regards, barry
0

LVL 29

Expert Comment

ID: 35191569
=COUNTIF(G1:G5000,"D")

gowflow
0

LVL 6

Expert Comment

ID: 35191577
first question: =SUMIF() would do that.
second question: not necessarily unique, but lookup will only bring one result, the first one it finds.

:)
0

LVL 7

Expert Comment

ID: 35191586
=SUMIF(B1:B5000,"D",G1:G5000)
0

LVL 29

Expert Comment

ID: 35191619
pls ignore my previous post barryhoudini your correct.
gowflow
0

Author Closing Comment

ID: 35420677
Thanks Barry!
0

## Featured Post

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…