Summing distinct rows in same cell

Sorry for the bad title I'm not sure hte best way to sum up this issue.

Fruit	Price	Total
Apples	0.69	40
Bananas	0.34	38
Lemons	0.55	15
Oranges	0.25	25
Apples	0.5	10
Pears	0.59	40
Almonds	2.8	10
Cashews	3.55	16
Peanuts	1.25	20
Walnuts	1.75	12
Apples	0.5	5

Given the data above I'm trying to sum the totals for only the Appl
rows.  So basically I need to search the Fruit column find the row
that have "Apples" and then sum their corresponding Totals, giving m
55.  I've been playing with this for hours with Lookups and Indexes bu
am not really getting anywhere.  Also, I cannot use any VBA as th
client will not run any files with Macros.  Thanks in advance for an
assistance.
Gre

--
summerg
-----------------------------------------------------------------------
summergs's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3741
View this thread: http://www.excelforum.com/showthread.php?threadid=57098

0
8/12/2006 6:01:48 AM
excel 39879 articles. 2 followers. Follow

2 Replies
556 Views

Similar Articles

[PageSpeed] 36

Theres a few ways

Here's a few of them

1) You could use a pivot table 

2)  =SUMPRODUCT(--(A2:A12="apples")*(C2:C12))

3)  =SUMIF(A2:A12,"Apples",C2:C12)

VBA Noo

--
VBA Noo
-----------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3383
View this thread: http://www.excelforum.com/showthread.php?threadid=57098

0
8/12/2006 6:30:20 AM
That did it thank

--
summerg
-----------------------------------------------------------------------
summergs's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3741
View this thread: http://www.excelforum.com/showthread.php?threadid=57098

0
8/15/2006 6:42:18 PM
Reply:

Similar Artilces:

Pivot Table
Hi, I have 'hours worked' as a row in a pivot table. I use the drop down arrow to filter/deselect the data I don't want, everything is fine and the totals are adjusted correctly. Lets say I only want the PT to show 'hours worked' >10. So I deselect what I don't need (everything <10). I now know this PT to be 'hours worked greater than 10' but I don't want to see each instance of 'hours worked' 10.5, 11.75 etc etc , just the totals by department (dept is also in data). But when I (right click and) hide the column it reverts back to t...

Have user input converted to uppercase in same cell as input?
Hi all Noob Alert! Very new to spreadsheets! I have a simple form that requires data selected from a text description to be entered in a cell to give us a special product code. The code is upper case in the description but ppl are entering it in lower case, I need to convert the entry to uppercase to match our entry system, but I need a function to convert and display the converted text in the original cell. I looked at =UPPER function but that displays converted text elsewhere, if I use it in the same cell as input I want converted I get the circular error msg. Can someone perhaps ...

Nested formular to give a word answer based on 2 cells
I have a s/s which I want to produce a statement based on 2 cells G170 is a cell that contails a sum (if cells above are filled it it will add them up) I170 contains a cost if G170 & i170 are blank then do nothing If G170 is greater than 0 and I170 is greater than 0 then again do nothing but if G170 is greater than 0 and I170 is = < 0 then "No Charge Made" Can anyone help all I get is FALSE I have tried =if(g170<=0, and (i170=<0,No Charge made","")) Hi, =IF(AND(G170="",I170=""),"",IF(AND(G170>0,I170...

How to Move First Row?
Hi I�ve just created a spreadsheet in Excel which goes into around 2 different columns now in my first columns I have a list of names whic I want to appear at the beginning of each page as I scroll across m Spreadsheet I was wondering if this is possible and if so How man Thank -- chiefbrutali ----------------------------------------------------------------------- chiefbrutalis's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3530 View this thread: http://www.excelforum.com/showthread.php?threadid=55080 Check out Window>Freeze Panes feature for locking rows ...

Summing Data based upon a condition
Hi, Trying to "Sum" data in one column (A) based upon a condition in another column (B)....... Any help? -- Chris Take a look SUMIF function in help menu "Chris" wrote: > Hi, > > Trying to "Sum" data in one column (A) based upon a condition in another > column (B)....... Any help? > > -- > Chris Hi, Let's assume you want to sum column B if column A equals customer "A" =sumproduct(--($A$1:$A$1000="A"),$B$1:$B$1000) "Chris" wrote: > Hi, > > Trying to &q...

Create a cell with only PART of another cells data
I have a column (D) with the following formatted data 0908-1234 0208-0691 0903-0256 I want to create a cell (column) that ONLY contains the last 4 digits 1234 0691 0256 Can some one please tell me how to accomplish this. Thanks Dave __________ Information from ESET NOD32 Antivirus, version of virus signature database 4443 (20090921) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com Try this: =3DRIGHT(D1,4) assuming the data starts on row 1. If you want it as a number, then you can do this: =3DRIGHT(D1,4)*1 Copy down as required. Hope this helps....

How do I edit directly in a cell
In earlier versions pressing F2 enabled editing directly in a cell. How do I do this in 2007 Office Button > Excel Options > Advanced > Allow editing directly in cells Then F2 will work as desired. -- Gary''s Student - gsnu201001 "Pweter" wrote: > In earlier versions pressing F2 enabled editing directly in a cell. How do I > do this in 2007 The shortcut-key remains same for XL2007 -- Jacob "Pweter" wrote: > In earlier versions pressing F2 enabled editing directly in a cell. How do I > do this in 2007 ...

When entering data into excel spreadsheet cell, the page just jump
I am trying to add data to an existing worksheet this morning. I can click on the clel, but when I try to enter the data, the worksheet "jumps", am unable to enter any data. What have I done wrong? If you are making entries using the numeric keypad, it sounds like you turned your Num Lock off. Try pressing NUM LOCK (there should be alight that comes on above it when you do) & try again. HTH|:>) "jodj" wrote: > I am trying to add data to an existing worksheet this morning. I can click > on the clel, but when I try to enter the data, the worksheet &qu...

optimising column width & row height
Hello, I have a table whose cells contain text. The lengths of the text strings are unpredictable, and vary quite widely between different records. To minimise the overall size of the table (for printing) I use 'wrap text' , and I juggle column width and row height by double-clicking on the row/column boundaries. This works, but I cannot seem to find an optimum, and every time I add new records I have to start again. Any ideas how to optimise the table size please ? Thanks KK Hi KK, How many columns of data is involved and what restrictions do you want to set on the number...

cell color does not show
When I fill a cell with color the color does not show on my worksheet but when I change to print preview I can see the color. How can I fix this? If the high contrast setting is turned on you won't see the fill colour. There's information in the following MSKB article: OFF: Changes to Fill Colour and Fill Pattern Are Not Displayed http://support.microsoft.com/?id=320531 mickid wrote: > When I fill a cell with color the color does not show on > my worksheet but when I change to print preview I can see > the color. How can I fix this? -- Debra Dalgleish Excel FA...

Cell formating
How do I format a cell such that I see a number say 40000000.00 as 4,00,00,000.00 ? -- IndianGuru ------------------------------------------------------------------------ IndianGuru's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27382 View this thread: http://www.excelforum.com/showthread.php?threadid=469097 Click Format then Cells. Select the Number tab. Click on Custom in the category box and in the Type box type #,###.00 "IndianGuru" <IndianGuru.1vngya_1127207106.465@excelforum-nospam.com> wrote in message news:IndianGuru.1vngya_11272...

converting general cells format to text
I'm having a hard time converting already existing general cells into text format. I'm trying to change it into text format so SAS will be able to recognize it. Without the "green indicator", SAS, for some reason, will treat it as a "general" cell even though I've saved it and changed the cells into text. Any suggestions? Sometimes when you type a number into a cell and then change the format to text, it does not "set in" imediately. Hit F2 and then enter on each cell and it should change it for you. Hope this helps. "junkgrrl" wrot...

Locking Named Cells
Lads, I have a lot of named cells in a workbook. Can I lock the name of th cells but allow users to change the data in them? I do not want them to be able to change the cell names becuase o references to them elsewhere in the workbooks. Lia -- wjoc ----------------------------------------------------------------------- wjoc1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1528 View this thread: http://www.excelforum.com/showthread.php?threadid=47473 Are you sure that the users are changing the names? Maybe they're just adding another name for that range...

Compare cells (advanced)
Hi there. I need to find a smart solution for comparing cells in an Excel file. To make a long story short, we usually receive excel sheets with thousands of rows. From a certain data there we make our own extraction from our database and put that in the same sheet, next to the other data. Then we need to compare some data, to make sure that it is correct. Lets say that we are comparing names. The problem is that VERY often the two cells containing the names are not _exactly_ the same. There might for example be one letter misspelled in one of the cells e.g. (Mike Brown/Mike Bruwn) or in one ...

Using a VLookup "type" reference but the result is the CELL not th
I'm using a VLOOKUP to help me select a date range from another work sheet. ie 6/01/04 VLOOKs up a date on another sheet and it's corresponding revenue number. The same is applied to 6/30/04. I'm trying to sum the revenue in the cells (days 6/01-6/30) via the VLOOKUP formula, but the VLOOKUP only returns the value and not the cell location. I need the cell locations to sum the revenue numbers in between. ie. the revenue numbers between B4 and B10. Any help would be greatly appreciated Hi try the following =SUMPRODUCT(--(A1:A100>=DATE(2004,6,1)),--(A1:A100<=DATE(2004,6,1)...

Distinct seems to use more CPU resources
I am trying to analyse the Profilers results whether taking out DISTINCT from my SELECT statement (with 4 unions) averages the CPU a little higher and the Reads a little lower. The Writes and Duration are about the same. Here are some of the results. This is running about 10 times each. In the Without Distinct, CPU was always 109 or 110 about the same distribution and he Reads were mainly around 7583. In the Wiith Distinct, CPU was had 3 @93 and the rest around 109 and the Reads were mostly around 7610. Not that much difference. Without Distinct: CPU ...

How do I insert a dot/check mark in a cell in Excel?
This is very basic! And I am having a problem... I want to insert a dot or check mark in a cell. Where can I find a dot/check mark? I thought they can be easily found, but I can't find them. Can anybody help? See if this helps, Sue: http://www.officearticles.com/misc/symbols_and_characters_in_microsoft_office.htm ************ Anne Troy www.OfficeArticles.com "sue" <sue@discussions.microsoft.com> wrote in message news:93425CB8-2CE6-4277-AA05-38F597DEB1F2@microsoft.com... > This is very basic! And I am having a problem... I want to insert a dot > or > ...

Naming sheets from a cell value
Hi there, Thanks for reading my question. Is it possible to name a sheet from a cell value with in the sheet? i.e. I have 12 sheets, each sheet represents a month and the month value is in cell A1. Can I rename a sheet by refering to cell A1 instead of manually renaming the sheets to January, February etc. Many thanks Tony See http://www.mcgimpsey.com/excel/events/sheetnamefromcell.html In article <127785E3-01B4-49B1-9966-8D79F795EFA4@microsoft.com>, "Tony4X4" <Tony4X4@discussions.microsoft.com> wrote: > Hi there, > Thanks for reading my question. >...

Different beginning cell each time for same macro
I would like my macro to begin in a different cell that I select each time and then have the first step of the macro select the 12 cells immediately to the right of the cell I select each time. Currently my macro runs perfectly except that the macro will only begin in the exact same cell every time the marco is run. This means that the same 13 cells are used by the macro every time. Where as I need the marco to begin in a different cell that I more or less randomly select and then the macro should select the 12 cells immediately to the right of that cell. I know someone out there...

Deleting Cells Option doesn't appear
I have a workbook with several tabs. Each sheet has command buttons and macros -- but the format is identical. I want to delete a few cells (not rows, just cells) On some of the tabs, I can select the cells, Edit / Delete / Shift Up However, on some of the tabs, I can select the cells and I only get the option to "Delete Row". I have compared the Options and other settings, and can't find what would be doing this. Any ideas Nevermind -- I finally found it -- I had a Filter that was on. Apparently, even if the filter is not in the area that you want to delete, it still ...

Summary in the last row
Hi, I'm need to make a vba macro where the macro automaticly shall find the last row and make a summary of the total column D. I cant get It right, can someone please help? So far I only has typed following. Dim lngLastRow As Long lngLastRow = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row Range("D" & lngLastRow + 1) = _ -- Best regards Mia Sub slr() dim mc as long Dim lr As Long mc = 4 'col D lr = Cells(Rows.Count, mc).End(xlUp).Row Cells(lr + 1, mc) = _ Application.Sum(Range(Cells(1, mc), Cells(lr, mc))) End Sub -- Don Guill...

How to specify a fixed cell in a formula
Perhaps I am explaining myself in the wrong termonology but when I drag a cell into multiple cells below it the formula changes cells. How do I make it from changing cells. -- Dave Dave, you need to use a absolute, or mixed reference, form excel help Switch between relative, absolute, and mixed references Select the cell that contains the formula. In the formula bar , select the reference you want to change. Press F4 to toggle through the combinations. The "Changes To" column reflects how a reference type updates if a formula containing the reference is copied two cells down an...

Freezing cells #4
I have some cells with numbers that rely on formulas with random numbers. Is there a way to save those values and make the cells independent of the formulas used to make them so that they do not continue to recalculate every time I type something? You can convert them into numbers. Copy the cells with the formulas. Then choose Paste Special on the Edit menu. Click Values and click OK. -- tj "Qban" wrote: > I have some cells with numbers that rely on formulas with random numbers. Is > there a way to save those values and make the cells independent of the > formul...

Cells that won't Lock!
I am using Excel 2007 and I need to lock two cells to prevent anyone removing the formulae. I highlight the two cells and on the Home tab / Cells / Format, I click on 'Lock' in the drop-down menu. However, having done all this I can still over-type in those cells and the formulae disappear. What am I doing wrong? Noel Cells are locked by default. So, locking the cells does nothing until you protect the sheet. Right click sheet tab>protection. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Noel S Pamfree" <Noel.spamfree@hotm...

Sum formula #3
Im trying to sum a column, but I only want to sum values were there is a certain value in another column, ie I want to sum each value in one column where it does not = R in the other column. Hi =SUMIF(RangeToCompare,"<>R",RangeToSum) -- (When sending e-mail, use address arvil@tarkon.ee) Arvi Laanemets "MRG" <anonymous@discussions.microsoft.com> wrote in message news:118b01c3fb7f$d9775280$a001280a@phx.gbl... > Im trying to sum a column, but I only want to sum values > were there is a certain value in another column, ie I > want to sum each valu...