#### Lookup and Sum in same cell

```SHNAME	ENTDATE	                SYMBOL	QTY	RATE	COMM
C78	1/24/2005	CSCO	1100	0.018	\$20
C78	1/28/2005	CSCO	-5439	0.018	\$100
C78	2/23/2005	CSCO	50	0.017	\$1
C78	4/5/2005	                CSCO       -7892	0.018	\$140
C78	4/13/2005	CSCO	3786	0.018	\$69

I am trying to create a lookup formula that will search by SHNAME, find
each entry and sum the COMM column in one cell:

SHNAME    COMM
C78           \$360

This worksheet is thousands of rows long with multiple rows for each
SHNAME.

--
------------------------------------------------------------------------

```
 0
10/3/2005 6:00:25 PM
excel.misc 78881 articles. 5 followers.

5 Replies
260 Views

Similar Articles

[PageSpeed] 20

```SUMIF should work for you.

--
shternm
------------------------------------------------------------------------
shternm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=858

```
 0
10/3/2005 6:06:47 PM
```Thank you for your help.

Now how can I get a list of SHNAME without duplicates

--
-----------------------------------------------------------------------

```
 0
10/3/2005 6:27:07 PM
```=SUMIF(A:A,"C78",C:C)

HTH
Bob

wrote in message
>
> SHNAME ENTDATE                 SYMBOL QTY RATE COMM
> C78 1/24/2005 CSCO 1100 0.018 \$20
> C78 1/28/2005 CSCO -5439 0.018 \$100
> C78 2/23/2005 CSCO 50 0.017 \$1
> C78 4/5/2005                 CSCO       -7892 0.018 \$140
> C78 4/13/2005 CSCO 3786 0.018 \$69
>
> I am trying to create a lookup formula that will search by SHNAME, find
> each entry and sum the COMM column in one cell:
>
> SHNAME    COMM
> C78           \$360
>
> This worksheet is thousands of rows long with multiple rows for each
> SHNAME.
>
>
> --
> ------------------------------------------------------------------------
> http://www.excelforum.com/member.php?action=getinfo&userid=20834
>

```
 0
10/3/2005 6:30:12 PM
```You could use AdvancedFilter | Unique Values for a unique list without
duplicates.

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)

wrote in message
>
> Thank you for your help.
>
> Now how can I get a list of SHNAME without duplicates?
>
>
> --
> ------------------------------------------------------------------------
> http://www.excelforum.com/member.php?action=getinfo&userid=20834
>

```
 0
firefytr (112)
10/3/2005 8:49:32 PM
```These suggestions work perfectly.  Thank you all very much for your
help!!

--
------------------------------------------------------------------------

```
 0
10/4/2005 11:29:56 AM

Similar Artilces:

If any cell in a range is blank question
Hello. I have two sheets in a workbook. One is a Summary sheet, and the next is titled "Bldg 5". What I am trying to make sure is that the range A1:K20 on the Bldg 5 sheet has had data entered to them (ie, they are not blank). The question that corresponds to Summary sheet A1 is "Has the data for Bldg 5 been updated?" If ANY blank in Bldg 5's range A1: K20, then put a "NO" in Summary A1's cell. If ALL of the Bldg 5 range had data in it, then put a "YES" in Summary A1. I also need A1 to update as data is entered into the Bldg 5 range (a...

Splitting a cell that has word wrap as the delimiter
Hi - I have an excel cell that has name on one line, then alt+enter, address on the next line, then alt+enter, state on the next line, etc... I want to split them - I tried using Data Text to Columns, but I can't figure out to use the Alt+enter as the delimiter. Any ideas? Check other and place the cursor in the box, hold down Alt while typing 010 on the numpad, release the Alt key -- Regards, Peo Sjoblom "Leslie" <anonymous@discussions.microsoft.com> wrote in message news:074301c3c3f9\$59b12340\$a001280a@phx.gbl... > Hi - > > I have an excel cell that ...

Changing color of a range of cells dending up on a value in anothr
Hi, I need to change the color of a range of cell, depending up on the value in another cell. I am using Excel 2003. And it allows me to give only 3 conditions when using conditional format. But I have 7 differnt conditions. Thanks in advance for any help. Srajes. Unless you move to XL2007, you will need to use VBA. Here are some sites that will help http://www.ozgrid.com/VBA/excel-conditional-formatting-limit.htm http://www.mvps.org/dmcritchie/excel/condfmt.htm Alternatively, there is an add-in here http://www.xldynamic.com/source/xld.CFPlus.Download.html -- Steve "Srajes&qu...

My goal is to have a text box that displays the contents of a particular cell. How do I link the text box to the cell? Thanks, Denise Hi denise, Make a Text Box from the drawing toolbar. Select the box and in the Formula Bar, type = and then select the cell, press enter. ( IE: =\$C\$5 ) The cell can also be on a different sheet, just do the same. ( IE: =Sheet2!C5 ) Dave denise Wrote: > My goal is to have a text box that displays the contents of a > particular > cell. How do I link the text box to the cell? > > Thanks, > Denise -- Piranha --------------------------...

Excel Crashes After Autosum or Sum
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Mac OS X 10.6.2 <br> Excel Version 12.2.3 <br><br>My Excel crashes every time I try to use the sum feature. It doesn't matter if I manually type in the formula and hit enter to sum, or if I hit the Autosum button to add up cells, it crashes every time. I only started to do this recently. I haven't made any changes or added any programs to my computer lately that I know of. Any help that you can offer would greatly be appreciated. Excel is basically useless to me at the moment without t...

How to you selectively sum on a series of numbers?
I have a column filled with numbers, but I only want to sum up a few of them. Let's say I have numbers filling A1:A10. Let's say I only want to sum up the numbers located is A3 and A4. I'd like to ability to enter something in the b column (e.g. "y" in cells b3 and b4) and have a formula that adds up the values in the "A" cells that have a corresponding "y" in the "B" column. This formula would allow me to selectively choose which cells needs to be summed. What is the best/cleanest way to write that formula? Thank you. =SUMPRODUC...

Truncated Merged Cells
Actually, I have truly MERGED cells that contain LINKS to other workbooks. The merged cells were created exactly as you described and the data within them (text) comes from several other workbooks, with the appropriate references. We created them in 95 and later converted them to 97. We can use them in Excel XP but they lose the data that would be displayed on 2002 and 2003. Have not tried 2000 yet. Thanks for the help. We are very concerned about this problem. Our workbooks are complex with many links. Elsa I only have the ideas that I already posted. And you can find posts in goog...

Is there a way to Sort Data by font color or cell shading?
I have a long list of items that I sort by either item number or date depending on what I'm looking for. From time to time these items status will change and I will gray them out by just shading the background of the cell gray. Is there a way I can sort these items by the background/shading of the cell first and then the secondary sort be date? So all the cells which contain gray shading will be first in the list (sorted by date if possible) then the rest of the list without the gray shading will follow by date as well. Thanks. Take a look at this link: http://www.cpearson.c...

Cell identity when printing comments on separate page
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel When printing comments at end of page, it identifies the cell by letters and numbers. (i.e. Cell: AA33) Afterward the comment is shown for that cell. <br><br>Question: Instead of the cell identity being shown this way, is it possible ithe cell can also be identified by its X axis header for that column with the y access header for that Row. <br><br>Thank for your assistance. <br><br>Carl Sorry Carl, but I'm not clear on what you're asking for... In 'MySpeak' ...

Assigning a color to a cell
I saw some other post about kind of the same problem but not this way. I have a risk evaluation formula in a cell. Output range is between 1 and 9 If between 1 and 3, I want to assign the color lightgreen to it If between 4 and 6 I want to assign the color yellow to it and red if 7 to 9. Is this possible within the Function use ? If not, How can I apply that automatically ? a macro ? How ? TIA Hi See menu Format > Conditional format. It has a very helpful wizard. (Remember to consider what should happen at exact numbers 3, 6, 9.) HTH. Best wishes Harald "Junkyard Engineer&qu...

Inserting an image into a cell
Is there any way to insert an image (or a series of images) into a cell rather than onto a worksheet? Many thanks hi unless they have changed something in 2007, about the only way you can do that is insert it as a comment. http://www.contextures.com/xlcomments02.html#Picture Regards FSt1 "Trent SC" wrote: > Is there any way to insert an image (or a series of images) into a cell > rather than onto a worksheet? > > Many thanks > > > Hi, Not into the cell but maybe you could use the comment to hold your image. http://www.contextures.com/xlcomments0...

Running macros for different cells at the same time
Hi all, Is it possible to run different macros on different cells at the sam time? If so how do I go about setting it up? Cheers Michae -- Message posted from http://www.ExcelForum.com Hi not really sure what you're trying to do. Could you give an example? -- Regards Frank Kabel Frankfurt, Germany > Hi all, > > Is it possible to run different macros on different cells at the same > time? If so how do I go about setting it up? > > Cheers > > Michael > > > --- > Message posted from http://www.ExcelForum.com/ Yes it is possible to run different m...

Word Wrap in Cells
Hiya, Have a strange one. Have word wrap set to on in cell format and have text in paragraphs (using alt-ret) within the cell. Some sentences (all within the same cell)are wrapped but others aren't. Any ideas? New one on me this. Many thanks, Shaun Hi if you have lots of text in your cell (>1000 characters) this could happen -- Regards Frank Kabel Frankfurt, Germany "Shawk" <shawk@clara.co.uk.3guesses> schrieb im Newsbeitrag news:1103641104.32476.1@demeter.uk.clara.net... > Hiya, > > Have a strange one. Have word wrap set to on in cell format and ha...

Splitting multiple cell contents from 1st column into 4 columns
n my first column I have 4 cells (1-4) (5-8) etc. with general content. The content from each of these 4 cells needs to be placed in a separate column to make a list that I can sort etc. Example: COLUMN A 1 01-04-425-001 2 Heatherridge Road #301 3 Harry Smith 4 H25 Condo Fairfax Place 5 01-05-356-041 6 McGrath Street # 56 7 Mary Jones 8 B45 Condo Lemon Circle . . . . . . and so on and so on every 4 cells. I have hundreds of 4 cell descriptions. I just can't do this one by one. Can anyone help, PLEASE, PLEASE. I am really a novice at Excel though I use it for e...

how read value from last selected cell? It is possible? how get adress last selected cell?
for example. I write to cell A11 value "hello", and next: IF I click "ENTER" or IF I click mouse in any different cell (A15, B10, C1... free choice) I wont to display: MsgBox "Value in your last selected cell = ???" On this example MsgBox "Value in your last selected cell = hello" it is very important for me. I try with event of Private Sub Worksheet_Change(ByVal Target As Range) ?? but :( thanks everybody, Andrzej Andrzej You have little choice but to use the Worksheet_Change() event as the Worksheet_SelectionChange() event, give you the cell ...

How do I pass series data x-value range to a chart from a cell?
I have a series on a line chart (x-values "=Sheet1!\$A\$1:\$A\$10"). I also have two worksheet cells (B1 & B2) that contain the start and end date for the x-axis of the chart. I want to be able to link the chart series data to the start and end date values so that the chart automatically adjusts when I change the dates in the worksheet cells. I can create a string or address for the x-values but how do I do not know how to pass this to the chart series?? I know I can do this using a macro, but I want to do it without macros so that friends who use MicrosoftCharts (the cheap, n...

Is there a way to protect individual cells?
Hello, is there a way to protect individual cells? Thank you -- amos7 ----------------------------------------------------------------------- amos79's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2759 View this thread: http://www.excelforum.com/showthread.php?threadid=47112 amos79 Wrote: > Hello, is there a way to protect individual cells? Thank you! Go to *Tools/Protection/Protect Sheet* Regards -- BenjieLo ----------------------------------------------------------------------- BenjieLop's Profile: http://www.excelforum.com/member.php?action=g...

This one is tricky....Macro to save file as cell value x in direct
Hi Two cells A1 & B2 I am trying to create a Macro to attach to a button to Save an .xls as "A1".xls in directory R:/SALES/Team 318/"B2" instead of typing it in all of the time.... Any helpers? Past attempts at this have failed.....the winner will get a big MVP vote! Absolute respect Andy Try something like: Sub Test() Dim mypath As String Dim sfile As Range, sdir As Range mypath = "R:\SALES\Team 318\" Set sfile = Sheets("Sheet1").Range("A1") Set sdir = Sheets("Sheet1").Range("B2") ThisWorkbook.SaveAs Filen...

Font sensitve lookup
Columns A:B contains videos and their retail price. I want to highligh in italics the rows of the items which are out of stock. I want to keep the price of the out of stock items in there fo reference but highlight them in italics. This is so I can predict th total sales without having to delete these rows. How do I create a formula that will sum the prices in column B of al the items except the ones in italics? Is this possible -- stev ----------------------------------------------------------------------- stevo's Profile: http://www.excelforum.com/member.php?action=getinfo&useri...

Walk list of lookup values with JScript
Hi All, New to the community. I have looked a bit in the threads and didn't get a clear answer on this question, so I am posting. I desire to create a case related to a contract and have the contractdetail lookup pre-populate with a value if it exists for that contract. Each contract can have many contractdetails, however we generally create a specific named contractdetail for each of our contracts, the catch is the name is the same, good, the guid is unique for each thus no hardcoding. Can anyone direct me on how to essentially walk a list of possibilities, much like the options i...

in VBA how to retreive cell values 1 byte at a time?
How would I write VBA to retrieve (get at) the value in a cell byte by byte from its natural form? i.e. no conversions are to be performed. E.g., if a string, then I may get more bytes than for a number. Thanks. Penny, If the cell contains text, you can retrieve characters (bytes) with the MID function. =MID(A2, CharPosition, 1). You can get the actual byte values with the CODE function =CODE(MID(A2, CharPosition, 1)). If a number, you can extract the decimal digits of the number in the same way, but you're not getting actual bytes (as stored by Excel). Excel stores numbers in ...

Can I format an entire row based on the value of a single cell?
I would like to use the conditional formatting feature to format an entire row rather than just a single cell. For example: If the cell value = "Total" then the entire row is bold. select your range (multiple rows???). Then with A3 (say) the activecell in that selection: format|Conditional formatting formula is: =\$a3="total" apply a nice format LTShelley wrote: > > I would like to use the conditional formatting feature to format an entire > row rather than just a single cell. For example: If the cell value = "Total" > then the entire row is bo...