Solved

Summing some of the values in a table in Excel.

Posted on 2011-03-22
6
259 Views
Last Modified: 2012-05-11
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
Comment
Question by:Steve_Brady
6 Comments
 
LVL 50

Accepted Solution

by:
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

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

gowflow
0
 
LVL 6

Expert Comment

by:FernandoFernandes
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 7

Expert Comment

by:harr22
ID: 35191586
=SUMIF(B1:B5000,"D",G1:G5000)
0
 
LVL 29

Expert Comment

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

Author Closing Comment

by:Steve_Brady
ID: 35420677
Thanks Barry!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now