#### Possible to do a calc on certain rows?

```Hi all,

In 23 days time I am off around the world for 10 years.  I have a pal
TX and have found that I can use excel spread sheets.  I already have
budget set in MS ACCESS that is accessable online, but I would like
budget in Excel so I can keep up to date as we travel around.

In my budget I have the following columns
(A1) Type (food, accommodation)
(B1)Price
(c1)Days
(d1)Place

then the following rows have the main information
(A2)accommodation
(B2)100.00
(c2)2
(d2)Auckland

(A3) food
(B3)60
(c3)2
(d3)Auckland

(A4)Sightseeing
(B4)40
(c4)1
(d4)North Shore

Now is it possible to do something like

select all days (column c) where type = food (column b)
and then sum the total?

The way that the budget (in access) works is that the days under th
food type is used to work out the cost per day, which is why I need t
be able to call the sum of the days for food only!

Advise on how to do it and how to enter it would be most appreciated!

thank you for your time :

--
kotitiaher
-----------------------------------------------------------------------
kotitiahere's Profile: http://www.officehelp.in/member.php?userid=504

Posted from - http://www.officehelp.i

```
 0
11/10/2006 1:07:52 AM
excel 39879 articles. 2 followers.

2 Replies
479 Views

Similar Articles

[PageSpeed] 47

```One option-

Assuming the data range is a1:d20, in G1,G2,G3 (whatever cells you wish)
enter:

=SUMIF(\$A\$2:\$A\$6,"food",\$B\$2:\$B\$6)
=SUMIF(\$A\$2:\$A\$6,"accommodation",\$B\$2:\$B\$6)
=SUMIF(\$A\$2:\$A\$6,"sightseeing",\$B\$2:\$B\$6)

In G4 just use =SUM(G1:G3)

If you need the number of occurrences for each category you can use the
COUNTIF in the same way.

HTH |:>)
Bob Jones
[MVP] Office:Mac

On 11/9/06 8:07 PM, in article kotitiahere.2h1ani@NoSpamPleaze.com,
"kotitiahere" <kotitiahere.2h1ani@NoSpamPleaze.com> wrote:

>
> Hi all,
>
> In 23 days time I am off around the world for 10 years.  I have a palm
> TX and have found that I can use excel spread sheets.  I already have a
> budget set in MS ACCESS that is accessable online, but I would like a
> budget in Excel so I can keep up to date as we travel around.
>
> In my budget I have the following columns
> (A1) Type (food, accommodation)
> (B1)Price
> (c1)Days
> (d1)Place
>
> then the following rows have the main information
> (A2)accommodation
> (B2)100.00
> (c2)2
> (d2)Auckland
>
> (A3) food
> (B3)60
> (c3)2
> (d3)Auckland
>
> (A4)Sightseeing
> (B4)40
> (c4)1
> (d4)North Shore
>
> Now is it possible to do something like
>
> select all days (column c) where type = food (column b)
> and then sum the total?
>
> The way that the budget (in access) works is that the days under the
> food type is used to work out the cost per day, which is why I need to
> be able to call the sum of the days for food only!
>
> Advise on how to do it and how to enter it would be most appreciated!
>
> thank you for your time :)
>

```
 0
11/10/2006 2:38:09 AM
```thank you so much CyberTaz - the first one worked perfectly!  :

--
kotitiaher
-----------------------------------------------------------------------
kotitiahere's Profile: http://www.officehelp.in/member.php?userid=504

Posted from - http://www.officehelp.i

```
 0
11/10/2006 3:54:05 AM

Similar Artilces:

Changing rows by Columns
Hi, I have a query. I need to show the result of this query changing rows by columns in the table of my report. How I can to make it in SSRS 2005? Best regards and thanks!! Julio I would T-SQL , a new PIVOT command does that "julito" <julito@discussions.microsoft.com> wrote in message news:E79F47EB-504E-494B-AA4F-EFEEFF510A8D@microsoft.com... > Hi, > > I have a query. I need to show the result of this query changing rows by > columns in the table of my report. > > How I can to make it in SSRS 2005? > > Best regards and thank...

Adjacent Cell to Last Cell in a Row
I am creating two columns where data is continually added. In one column I have units either lbs or kgs listed. In the second column I have a price. I want to create a formula that will pick the last entry in the units column and depending on whether it was lbs or kgs multiply the number in the cell beside it in a further calculation lbs 3.34 kg 5.67 kg 4.50 lb 8.94 lbs 8.94 kg 5.82 lbs 4.33 lb 6.22 lbs 2.68 kg 7.48 In this case in the first example the formula...

How to delete rows automatically with values zero out?
Hello, I have a spreadsheet with hundreds of rows. Many of them were entered at the beginning and then were reversed out. I got this spreadsheet from our ERP system. It is just like this: Part# Quantity Value 013-001 1 \$1.00 013-001 -1 \$1.00- 013-002 5 \$25.00 013-007 3 \$120.00 013-007 -3 \$120.00- 022-001 12 \$17.00 041-009 7 \$251.00 041-009 -7 \$251.00- 052-061 10 \$30.00 How can I delete those rows autometically with the quantity were zero out. I don't want them show up on my spr...

Report that display percentage below certain level
I got a spreadsheet contain inventory number from remote site. I need to run a report to display when inventory below a certain percentage. I try to use privot table to display the infomration. But it doesn't display everything below 40%. Is there anyway you can enter an percentage and display all remote site that below certain percentage (Eample: like 35%) on a report? Thanks. TSS, A pivot table groups like items, and makes a calculation on each group -- not what you need. You need a filter (Excel's way of saying "query" in database parlance). If your table...

Counting rows of blanks across certain columns
I have a survey whose answers were recorded in Excel. The answers for a particular question extend from Q6 to Z505. I need to count the people who did not answer the question (that is, the people, entered in rows 6 to 505 that left columns Q through Z blank). I'm not sure how to do this. Can someone offer a suggestion? Hi You can use a formula like this in Column AA =IF(COUNTA(Q6:Z6)=0,1,"") copy down till AA505 You can use a simple Sum formula now to count the empty ones =SUM(AA6:AA505) -- Regards Ron de Bruin http://www.rondebruin.nl "crossingboston" <...

Macro to insert to move values of a cell to next cell in the same row
Hello, I would like to know the Macro code for Moving the Values of a cell to next cell based upon values in a particular cell. Ex:- Col A(DATE) Col B(0-30 Days) Col C(30-60days) ColD(60-90day) Jan-05 10 Feb-05 10 Based upon date in Col A i want move the value in col b and c to the next cell.Basically this for aging the items in the work sheet Hi There, Can anyone pls give the code for this query. Pls help ...

Using possibly If and Vlookup
I have a spreadsheet where I want to first of all look up hours worked in cell N2 (within this cell is the number 35.00 or the number 37.00). I then want to use this cell to do a lookup bringing back one salary if N2 is 35 or another salary if N2 is 37. In my main spreadsheet I have a spinal column point in cell J2. I have created two lookup tables on another spreadsheet named salary35 and salary 37. These tables show the spinal point in one column and the appropriate salary in anoter column. eg salary35 1 11187 3 11534 5 11899 7 12246 9 12629 salary 37 1 11827 3 12193 5 12579 7 12...

Adding sums in rows and columns and colouring cells with conditions
Hi all, I have a long table where i need to add and colour individual cells based on 'days home' and 'days away'. The table is uploaded here https://www.yousendit.com/download/MnFqaUNBUzhoMlYzZUE9PQ In order to automate some of this i'd like to automate some, but preferably all aspects of this, such as: 1. automatically colour the cell orange for "home" and yellow for "away" 2. add separately in the home and away columns the number of days for each 3. add the rows for the alternate lines (ie days only, rates only) I want to do this without yet another...

Pasting in Rows?
Hi. Would anyone be so kind as to enlighten me about a simple pastin procedure in excel? I have a list (which I copied from a vertica column) that I want to paste into a horizontal row in Excel. I jus don�t see an option for this and the data gets put in vertically. Ther are hundreds of cells to paste so I�m hopeful that someone with mor experience can shed some light on the situation. I had the same proble with Access. Thanks in advance for any help you can offer -- FattyLumpki ----------------------------------------------------------------------- FattyLumpkin's Profile: http://www.e...

Hi- I am trying to link rows (that are infinitely long in terms of data) between two different worksheets so that I dont have to retype all of the data. (I want them to be active links, so that if the data changes, it will automatically update). Is there a way to do this? (The first worksheet is a master that has a whole lot of information, and I would like the other worksheets to reference rows from the original...) Thank you very very much.. In the other sheets you will have to set then up eg: in sheet2 select the cell you want then type an equals sign = got to master sheet and se...

Pressing Enter to skip rows
We have a pro-forma in Excel for clients. Anyway, the cells where the enter information from a drop-down list are three rows apart, goin down the page. Is there anyway for the client to press Enter just once and go thre rows down rather than pressing enter three times? I am aware that if the sheet is locked with the particular input cell unlocked then Tab will take you to the next box however, we need Ente to do the same. I imagine a macro could achieve this. Thanks in advance D -- DoctorWar ----------------------------------------------------------------------- DoctorWard's Profile...

Get External Data
I have a 2 column table which is likely to change length whenever the Refresh Data menu option is selected. The table links to another software package (SAGE). I don't have formulae in adjacent columns so my problem can not be solved by checking the "Fill Down formulas in columns adjacent to data" box. I do, however, have text in adjacent columns (which the user selects from an in-cell drop down list) which I need to remain aligned with the relevant row. The "insert entire rows for new data ..." option does not appear to do what it says! It only inserts rows in the...

Inserting rows into sheet that contains a formula
I have a typical financial sheet where the columns sum (sum(a5:a15)) and the rows sum (sum(a5:h5)). The summation columns are locked and the sheet protected. Is there a technique whereby I can have the user insert a row which will keep the formats and row sum as well as changing the formula in the column sums? Thanks. Rick Hi Rick, The following method is not foolproof but generally it works OK. Insert a blank row between the bottom of data to be summed and the row with the sum formulas. Include the blank row in the sum formula. Protect the blank row along with the formulas and what...

Modal popup with gridview row detail
Hi! I've show many example but I don't know how to apply solutions to my code: I've a gridview with a list of records, I'd like using a detail button, open an ajax modal popup with the detail (for update) of the row selected <asp:GridView ID="gvElencoAttivita" runat="server" AutoGenerateColumns="False" DataKeyNames="Id" onrowdatabound="gvElencoAttivita_RowDataBound" ShowFooter="true" AllowSorting="True" CellPadding="8" GridLines="Vertical"> <Columns> ...

how do automatically change entire row color when cell changes
Hi, is it possible to change the color of the row cell1 is in when cell1 changes to a specified text? See Help under Conditional Formatting -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "IOE" <IOE@discussions.microsoft.com> wrote in message news:91738C3F-3BEB-4EAE-8551-99E60D8E8A21@microsoft.com... > Hi, > is it possible to change the color of the row cell1 is in when cell1 > changes > to a specified text? Hi - select the row (e.g. row 1) - goto 'Format - Conditional Format' - enter the following formula =\$A1="T...

Repeat values in column A a certain number of times depending on the value in column B
Imagine a set of data as set out below: Column A Column B Apples 24 Pears 36 Oranges 8 I want to poplulate a column (for exampel column A on a new sheet), where the values in column A will be repeated as many times as the value in column B Thus the first 24 rows will say Apples, the next 36 rows will Pears, Oranges. I need a formula that recogonizes that when it gets to row 25 that it should no longer need to copy Apples, but then switch to pears. This might sound like an unusual request but if I can grasp a way to do this I can create the table I need and populate the rest...

Can someone help? This is the first time I have asked for help. I am new to Excel and have just developed a spreadsheet that adds up several columns for me. I have, it seems, applied the correct formula to work out different percentages in several columns for me. I am quite pleased with myself. I had to delete several rows from the spreadsheet but I selected "Hide" instead of "delete" on the rows that I wanted to remove! I realise now that the text has been hidden away but the total still includes the figures in that row! How can I reveal the rows that I have hi...

Macro to delete rows 03-17-10
I'd appreciate some help in creating a macro to delete rows. Here's what I need: 1) Delete rows 1-13 2) Then find all rows that are highlited blue and delete them 3) Then find all rows that include the text "Distr" and delete them Suggestions? Thanks in advance. Tell us how the rows came to be blue... -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "JeffF" <JeffF@discussions.microsoft.com> wrote in message news:863D165C-D100-42E5-9294-3569F1829DF0@microsoft.com... > I'd appreciate some help in creating a...

Combining certain matrices
Can anyone think of a way to add certain matrices together, depending on some matrix criteria? Situation: X Y Z Bob A 2 3 1 Bob B 4 1 5 Bob C 3 2 6 X Y Z Sue A 4 3 3 Sue B 4 1 5 Sue C 2 2 5 X Y Z Bob A 2 3 1 Bob B 3 5 5 Bob C 5 2 1 X Y Z Sue A 3 3 1 Sue B 4 1 2 Sue C 2 5 6 I want to combine all the matrices that belong to Bob, etc (ie. add all A-X together, all B-X, e...

Custom auto-sorting...possible?
Is there a way to have excel auto-sort (cutom) by criteria in 3 columns? I think it might be easier to explain by viewing the workbook http://poolgod.zftp.com/predictions.xls. The picture shows how I need it to sort. I found some information on auto-sort in help and also on the net, but I don't know how to incorporate the multiple criteria or if it's even possible. Any ideas? Thanks, Steve -- Pools ------------------------------------------------------------------------ Pools's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35961 View this thread: ht...