Is there any way to...

Hey there, New to excel and trying to figure out how to set up a formula 
that would let me use one Column (C) to decide whether or not a value in 
the same row but in column b gets used in a =Sum function.

Basically I have a list of items in column A, Their prices in column B 
and a Total in Column D for all the prices. Now, since some of the 
things in column A are packages of several items at a lower price then 
the items individually the only way to get just the value with the 
packaged deals is to edit the =Sum function to skip over the cells with 
the individual items prices ( least as far as I can see) which is a pain 
in the butt.

So i tried to set up column c with an =if function using "Y" as the 
logical test ( so i could hit y next to the different items and easily 
check out different 'purchases' ) and just couldn't get it to work.

So, Is there a way to use one column ( or cell ) to decide whether 
another cells value will be added in a sum function?

Any help would be greatly appreciated.

0
8/13/2003 6:36:25 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
253 Views

Similar Articles

[PageSpeed] 16

Ummmm
You're going to have to explain that in a bit more detail.
>-----Original Message-----
>Hey there, New to excel and trying to figure out how to 
set up a formula 
>that would let me use one Column (C) to decide whether or 
not a value in 
>the same row but in column b gets used in a =Sum function.
>
>Basically I have a list of items in column A, Their 
prices in column B 
>and a Total in Column D for all the prices. Now, since 
some of the 
>things in column A are packages of several items at a 
lower price then 
>the items individually the only way to get just the value 
with the 
>packaged deals is to edit the =Sum function to skip over 
the cells with 
>the individual items prices ( least as far as I can see) 
which is a pain 
>in the butt.
>
>So i tried to set up column c with an =if function 
using "Y" as the 
>logical test ( so i could hit y next to the different 
items and easily 
>check out different 'purchases' ) and just couldn't get 
it to work.
>
>So, Is there a way to use one column ( or cell ) to 
decide whether 
>another cells value will be added in a sum function?
>
>Any help would be greatly appreciated.
>
>.
>
0
rob3439 (3)
8/13/2003 7:38:34 PM
My Advice would be to use the IF() Function in Column C to decide if the Row
is to be calculated.  Mark by use If this... mark X if not... leave blank.
Then use the SumIf() function to sort through Coulmn C and to sum the totals
for anything that may be marked with an X.

SumIf(range,criteria,sum_range)

"kbriand" <briand@ant.eastlink.ca> wrote in message
news:Myv_a.56792$PD3.4580863@nnrp1.uunet.ca...
> Hey there, New to excel and trying to figure out how to set up a formula
> that would let me use one Column (C) to decide whether or not a value in
> the same row but in column b gets used in a =Sum function.
>
> Basically I have a list of items in column A, Their prices in column B
> and a Total in Column D for all the prices. Now, since some of the
> things in column A are packages of several items at a lower price then
> the items individually the only way to get just the value with the
> packaged deals is to edit the =Sum function to skip over the cells with
> the individual items prices ( least as far as I can see) which is a pain
> in the butt.
>
> So i tried to set up column c with an =if function using "Y" as the
> logical test ( so i could hit y next to the different items and easily
> check out different 'purchases' ) and just couldn't get it to work.
>
> So, Is there a way to use one column ( or cell ) to decide whether
> another cells value will be added in a sum function?
>
> Any help would be greatly appreciated.
>


0
f120st (1)
8/13/2003 7:39:07 PM
Thank you, this works like a dream. I don't know how to thank you enough.

Dan E wrote:
> Prices in B
> Y 's in C next to items to be summed
> 
> =SUMIF(C1:C4,"Y",B1:B4)
> 
> Dan E
> 
> "kbriand" <briand@ant.eastlink.ca> wrote in message news:Myv_a.56792$PD3.4580863@nnrp1.uunet.ca...
> 
>>Hey there, New to excel and trying to figure out how to set up a formula
>>that would let me use one Column (C) to decide whether or not a value in
>>the same row but in column b gets used in a =Sum function.
>>
>>Basically I have a list of items in column A, Their prices in column B
>>and a Total in Column D for all the prices. Now, since some of the
>>things in column A are packages of several items at a lower price then
>>the items individually the only way to get just the value with the
>>packaged deals is to edit the =Sum function to skip over the cells with
>>the individual items prices ( least as far as I can see) which is a pain
>>in the butt.
>>
>>So i tried to set up column c with an =if function using "Y" as the
>>logical test ( so i could hit y next to the different items and easily
>>check out different 'purchases' ) and just couldn't get it to work.
>>
>>So, Is there a way to use one column ( or cell ) to decide whether
>>another cells value will be added in a sum function?
>>
>>Any help would be greatly appreciated.
>>
> 
> 
> 

0
8/15/2003 7:29:02 PM
Reply:

Similar Artilces:

Best way to get a pick-list on a form?
Is there a safe and effective way to get a pick list of values for a Standard Outlook form? Maybe a Macro button? What I want is to limit and auto-enter some properties, without having to re-do an entire form. Simple, or no? -- dBaser from back in the day ... It might be simple, but it's hard to say without knowing exactly what you mean by "standard Outlook form" and what business goals you're trying to accomplish, in what version of Outlook. Please provide more details. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Programming: Ju...

Best way to deal with contacts and commercial opportunities
Sorry for the misleading title. We are using Exchange Server 2007 and Outlook 2003/2007. I'd like to create company's address book by using a Contact Public Folder, containing all our commercial contacts. I'd like these contacts to be assigned to different categories (geographical area, customer, product types of interest and so on) Any contact may potentially belong to more than one category. At the end I will need to send customized newsletters and promote marketing campaigns, depending on contact's categories. I was looking for Business Contact Manager, but I do...

Is there a way via GPO to remove the "Reply to All" option on the Standard Menu but keep in the Drop Down
I am looking for a way via Group Policy to remove the "Reply to All" button in Outloook 2000 and 2003 but keep the option active in the Drop Down menu (Actions/Reply to All). I've tested via Sue Mosher's solution using GPO but the Control ID 355 ties both functionality together. Is there a way to do the above via policy? Thanks Ariel magic1_aad@yahoo.com No, there is no way to do that with Group policy. Why would you want to = remove it in one place but not in all?=20 --=20 Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for=20 Adm...

How many ways to copy data?
I realize in Excel there are many ways of doing the same thing, but I'm curious. Just for kicks, can anyone tell me how many ways there are (or that you can think of) to copy data from one cell to another? My co-worker claims there are 4 ways (File/Copy, the ctrl- c/ctrl-v keyboard shortcuts, the copy/paste buttons, and through VBA code). I'm thinking there are more. I know you can use named references to refer to other cells, and in a sense, copy the data from one area to another by refering to the referenced name. I'm sure there are custom functions that could be cre...

Excel Hyperlink: Is there an easier way to follow then double-cli.
Excel Hyperlink: Is there an easier way to follow an Excel Hyperlink then with mouse double-click? My old version of Excel all you had to do was hit "Enter" In xl2002, I only have to use a single click. So that saves some time <bg>. This has been asked before and it looks like just selecting the cell and hitting enter is gone. Bob Small wrote: > > Excel Hyperlink: Is there an easier way to follow an Excel Hyperlink then > with mouse double-click? My old version of Excel all you had to do was hit > "Enter" -- Dave Peterson ...

Easiest Way to Move Column
I know I have seen this answer before, but I can't seem to locate it this time. What is the easiest way to move a column of data to a different column in one step, without overwriting any other columns, and without having to first insert a column, and without having to go back to delete the original column? Thanks, Carroll Rinehart Hi Carroll move your cursor under the column heading of the column you want to move and hold down the RIGHT mouse button ... drag the column to its new location and let go - you should get the option of SHIFT RIGHT AND MOVE .. Cheers JulieD "Car...

Calculating Date difference in 2 ways
Hi, If in cell A1 I write -- = today() - date(2004,12,31) then the answer I get is a number ( let's say 14 or 15) But if in cell B1 If i write -- = C1 - D1 where C1 -- = Today() and D1 -- = date(2004,12,31), the answer I get is in date format (in excel's 1900 base year) Why is there a diference in formatting of results between the above 2 approaches. Regards, Hari India Hi Jim and Aladin, I have not written my initial query properly. My doubt is that inspite of the same formulas and with both A1 and B1 having no previous formating, what causes excel to interpret these 2 approac...

What is the simplest way to print multiple worksheets?
I have a workbook with 30 worksheets of charts and 40 worksheets of data that feed the charts. Now I like to find the easiest way to print all 30 charts from this workbook at one simple command without printing the 40 data worksheets. Yes, I know I can click all the tabs while holding down the Ctrl key and then print. But I still have to click 30 tabs!!! Is it possible to set the print area across the 30 worksheets that contain the charts? By the way, the print set up for each chart is exactly the same. Any help will be very much appreciated. If your charts are all on CHART sheets in...

simple way to do a server-side autoreply in E2K ?
Hi all....looking for a little advice once again =/ I'm looking for a simple way to make E2K reply with a blurb anytime email is sent to a particular email address. I would prefer not to have to involve Outlook with some client-side rule.....was just hoping for a way to make E2K kick off an autoreply. So far, the only thing I've done in Global Settings | Internet Message Format | Advanced Properties of Default, is enabled Automatic Replies. Some of the posts mentioned using event sinks, which I know nothing about. Any ideas? Thanks in advance! Robert Robert wrote: > Hi all.....

LONG
Hi all, I have a .PST file I've been moving from system to system over the years that I use primarily to keep notes, contacts, appointments, task lists, etc. I do NOT use this PST file to store emails, so I end up with a useless "Inbox" entry that I can't get rid of in the main tree, but that's a non-issue. Typically, on all my machines, I move the entire "My Documents" folder from its default location so it's got its own partition (J: drive), which contains a "PSTs" folder. So, "My Documents\PSTs" is actually "J:\PSTs"...

Easiest way to create forms with Excel?
Hi all - apologies if this is a FAQ question, was unable to find good answers there. My organization often needs to distribute various 'forms' designed in Excel. People will fill them in, (often print a copy and) send the filled-in sheets back to us. There are a lot of problems with this: people will modify stuff where they're not supposed to, will leave 'mandatory' options blank, and the process of copying/pasting data for further processing once we get the filled in sheet is laborious. For various reasons we need to stay with Excel (i.e. Access, or redoing everything a...

is there a way to do this automatically
is there a way to do this automatically setting prices a, b, c Hi Victor, Have you tried Mihir's suggestion? See his reply to your original post. This should work for you, if you are unsure of how to do it reply to his message he left in your original thread. And don't forget to backup your data first :) hth kevin "Victor" wrote: > is there a way to do this automatically setting prices a, b, c ...

Best way to measure investment performance?
I'm using MS Money Premium 06 and wonder what the best way is to measure quarterly and annual investment performance, given that cash flows in/out and stocks are bought/sold during the period being measured. There are several Portfolio views that provide a general total return for 3 and 12 months but it doesn't look like it takes into account the above issues. Perhaps a Report is more appropriate? Any assistance is appreciated. Thanks, Miles In microsoft.public.money, Miles wrote: >I'm using MS Money Premium 06 and wonder what the best way is to measure >quarterly...

any way to combine registers so z-out contains all?
Is there any way to combine all registers so that one z- out would contain all data from days work on different registers? We would like to be able to use one actual cash register, but be able to sell from multiple registers or work stations. Is this possible? As far as I know and understand you must z out each individual register. We have 3 machines that can do the register but use 1 everyday. The other 2 machines we z out after they are used for sales. So one might have a batch from 4/30/04 to 7/1/04 if it wasn't used for sales during that period. -- Elizabeth M. "JackT&qu...

Easiest way to copy a single record from VB?
Hi All, Assume I have a recordset handy, and I'm looking at a record I want to copy. What is the simplest way? In the past, I've used AddNew, then copied contents field by field. Is there a better way? Thanks, Max You can highlight the entire record by clicking to the left of the first field. Copy the entire record. Then go to the end of the recordset and paste the enitre record in the last row (last row will always be blank) "Max Moor" wrote: > Hi All, > > Assume I have a recordset handy, and I'm looking at a record I want to > co...

Correct way to link IV30300 & IV30500
I'm trying to build a Smartlist that combines the data from IV30200, IV30300 & IV30500. I;m trying to reproduce the canned smartlist Inventory Transactions, but with the notes attached. IV30200 & IV30300 are a no brainer, but I can't come up with a combination of columns that link IV30300 and IV30500 in a many-to-one relationship. I seem to be getting cross-join-type results. Thots? Ken, Are you sure IV30500 is that table you want for notes? Unless I am missing something, there are no notes in the IV30500 table, just GL distributions. Can you clarify specifi...

Easy way to delete names in a workbook?
I inherited a workbook. The guy defined about 50 names, referring to a bunch of other workbooks. Is there an easy macro to delete all defined names rather than doing it one by one? Try Jan Karel Pieterse's Name Manager utility. It's a free add-in that you can download here: http://www.bmsltd.co.uk/MVP/Default.htm Joe wrote: > I inherited a workbook. The guy defined about 50 names, > referring to a bunch of other workbooks. Is there an easy > macro to delete all defined names rather than doing it one > by one? -- Debra Dalgleish Excel FAQ, Tips & Book...

Easiest way to "transpose" data?
I have a table, laid out as follows: Acct, Year, period1, period2, period3, period4,...through period13 Sales 2006 $1000 $2500 $3100 $1250 What is the easiest way to return each record as 12 records, Acct Year Period, Amount Sales 2006 Period1 $1000 Sales 2006 Period2 $2500 Sales 2006 Period3 $3100 Sales 2006 Period4 $1250 I can't mess with the table structure, and I actually have a bunch of different tables I will need to view in a similar fashion. I can figure ways to do it, but I am sure some of the genius level talent here has an easy way to do this? Phil If we were ...

Return calcs in Portfolio Manager way off after transfering in sec
I recently transfered some stock positions from an E-Trade account to my Fidelity account. The history of puchases is correct, but the return calculations are wacko. One loser shows an anualized return of a 100% loss. Under investment information, only the transfer is shown. But, under "see investment activities", the various purchases are shown. What is wrong and how can I fix it? ....Rich I have a similar problem. My Cash Reserves account shows a 3 month return of 6% (no way) and a YTD return of 0.4% (probably correct). Yet the downloaded cash movements and dividend pay...

Question File Sharing Only Working One Way
The problem I'm experiencing is an inability to open or save Microsoft Office files (Word and Excel files, in particular) while operating on a very small and simple home office network. We have a desktop system running Windows 7 as well as Microsoft Office 2007. We also have an iMac running Microsoft Office 2008. That is the full extent of our computer network (connected through Apple's Time Capsule router). We of course have file sharing turned on for both computers. I have no problems in opening or saving files when I'm operating on the iMac: I can open and s...

Any way to customize asset allocation categories in Money 2004?
I want to be able to assign my own asset allocation categories to Microsoft Money to be able to group by them. For instance, Money would view a precious metals mutual fund as only a "mutual fund", but for me, it's really a separate investment class I want categorized under "precious metals". I'm pretty sure these categories aren't editable, so I was wondering if anyone has found a workaround (besides creating a separate Excel spreadsheet, which I already did). Thanks. Many of us wish for it. Instead we get Essential * and turn to Excel. <matty_lewis@yah...

Is there a way in Exchange 2000 to export a list of all the mailboxes over a certain size?
Is there a way in Exchange 2000 to export a list of all the mailboxes over a certain size? At the moment I am just going in Exchange System Manager and then trawling through all the storage groups to check the numbers. It is kind of laborious to check the size of mailboxes and the number of messages one at a time, so was wondering if there is a way to do this automatically within Exchange, or do I need to use third party tools? Thanks, QH This is possible if you have Crystal. There is a 3rd party, will have a poke around now Yes it is possible to get a list of mailboxes over a certain s...

Any way to pretty this up?
I am trying to copy data from "Engine Data" and paste it into a new sheet. Engine Data contains many columns of FG_HC... I only want the one under the [Hertz] heading. The format of the Engine Data looks like the following: [Mode] FG_NOX FG_HC FG_CO data data data [Hertz] FG_NOX FG_HC FG_CO data data data etc Here is my code: Sheets("Engine Data").Select Cells.Find(What:="[Hertz]", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ ...

I have found a efficient way to flirt...
Hello ! I just wanted to answer you that finally I found a very efficient solution to solve my love and flirt problems. Everything is on the site www.cyclea.com There is a brand new method that indicate you how to proceed to become the best flirter you could never imagine. You can also ask for advices online to a specialist in Flirt, that's wonderful ! So just have a look... (-: Bruno :-) ...

Best way to add a row to a table with calculations?
Suppose I have a worksheet like this: 1 2 Date Days 3 5/25/08 35 4 4/20/08 45 5 3/06/08 46 6 1/20/08 41 ... The value in the Date column is calculated (=A3-A4). I would like to add a row between rows 2 and 3 and have it acquire all of the properties of row 3. The best procedure I could come up with is: 1. Right click on row 3, select Insert. This inserts a row between 2 and 3, but it acquires the properties of row 2. 2. Select row 4 (old row 3) and press Ctrl-C (copy). 3. Select row 3 (new row) and press Ctrl-V (paste). This seems to work. Is there a better wa...