 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2008Summing based on various critiera
I have been using a formula provided by Bob Phillips here to determine who would the total number of t-shirts by size based on monies raised. In the past, it has been the same amount of monies for everyone. Now, we are using one amount for adults and one amount for kids. The old fomula was: =SUMPRODUCT(--($C$6:$C$20=F24),--($F$6:$F$20>99)) Can someone tell me how to change the formula where the goal is $50 for kids and $100 for adults? Thanks in advance and I am using an old username when I need a quick answer. What determines who is an adult and who is a kid? What is in the cells in the formula you submitted? We have no idea what your original question was and what your formula means. Mind readers, we are not. Tyro >I have been using a formula provided by Bob Phillips here to determine who > would the total number of t-shirts by size based on monies raised. In the [quoted text clipped - 11 lines] > Thanks in advance and I am using an old username when I need a quick > answer. Good point, Tyro. Sorry I have a formula that determines if they get a shirt based on the dollar amount (currently $100). If they do, my formula will result in "Y". I have table set up as shown Col A = Shirt size (ex small) Col B = =SUMPRODUCT(--($C$6:$C$20=F24),--($F$6:$F$20>99)) to get the total of each size. Now, I have the two critiera, one for adult, one for youth. I know I may need to add at col. that indicates adult or youth. Which I don't have a problem with other than I don't know how to write the formula. Thanks > What determines who is an adult and who is a kid? What is in the cells in > the formula you submitted? We have no idea what your original question was [quoted text clipped - 17 lines] > > Thanks in advance and I am using an old username when I need a quick > > answer. What is in cells C6:C20 and what does it mean? What is in Cell F24 and what does it mean? What is in cells F6:F20 and what does it mean? What is the meaning of > 99? You are making this difficult. You know what your data means. I do not. Tyro > Good point, Tyro. Sorry > [quoted text clipped - 34 lines] >> > Thanks in advance and I am using an old username when I need a quick >> > answer. C6:20 = Shirt sizes (determined from a lookup table from a download) F24 – is the start of a shirt size table F25 would be the next shirt size F6:20 – is the total each person raised >99 is to determine who raised at least $100. Col a Col B Col C Col D Name Size Total Raised Get a shirt 1 Mary Smith S $100 Y 2 Joe Jones L $20 3 Billy Jones YL $50 Y Mary gets a shirt because she raised $100 and she is an adult. Joe does not get a shirt because he is an adult and he is below $100. Billy gets a shirt because he is a youth and he raised $50. I need to total each shirt size based on adults reaching $100 and youth reaching $50. I can add a col to show who is an adult and who is a youth if it would make the formula easier. You're getting closer. But what tells me Mary Smith is an adult and the Joe Jones is an adult and that Billy Jones is a youth? What I'm saying is, all detail is required to generate a solution. Tyro > C6:20 = Shirt sizes (determined from a lookup table from a download) > F24 - is the start of a shirt size table F25 would be the next shirt size [quoted text clipped - 17 lines] > if > it would make the formula easier. Right now, nothing. But as I said, I can add a column that would have A for adult and Y for youth. I would make it Col B and move everything else over 1 col. > You're getting closer. But what tells me Mary Smith is an adult and the Joe > Jones is an adult and that Billy Jones is a youth? What I'm saying is, all [quoted text clipped - 22 lines] > > if > > it would make the formula easier. You want your calculation to be include the factor of being an adult or child, as I understand it. Therefore, the information must be provided to the formula. The formula does not know the people involved. Tyro > Right now, nothing. But as I said, I can add a column that would have A > for [quoted text clipped - 32 lines] >> > if >> > it would make the formula easier. I would suggest you add a column for Youth or Adult and not pull it directly from shirt sizes. I'd be willing to bet there are some kids wearing adult sizes and some adults wearing youth sizes.  SignatureHTH, Barb Reinhardt > C6:20 = Shirt sizes (determined from a lookup table from a download) > F24 – is the start of a shirt size table F25 would be the next shirt size [quoted text clipped - 14 lines] > reaching $50. I can add a col to show who is an adult and who is a youth if > it would make the formula easier. NeedHelpFast - I suggest you set up your data in a standard flat-file database arrangement. Then it will be easy to answer this question (and others) using a pivot table. - Mike Middleton http://www.DecisionToolworks.com Decision Analysis Add-ins for Excel >I have been using a formula provided by Bob Phillips here to determine who > would the total number of t-shirts by size based on monies raised. In the [quoted text clipped - 11 lines] > Thanks in advance and I am using an old username when I need a quick > answer. I don't think the OP has any clue about what a flat-file is let alone a database arrangement. Tyro > NeedHelpFast - > [quoted text clipped - 23 lines] >> Thanks in advance and I am using an old username when I need a quick >> answer. Tyro, I know full well what a flat file is and I know what a database arrangement is. I appreciate you trying to help, but you don't have to act like a jerk because I couldn't make my problem clear to you. No more replies are needed or wanted from you. Thanks anyway. > I don't think the OP has any clue about what a flat-file is let alone a > database arrangement. [quoted text clipped - 28 lines] > >> Thanks in advance and I am using an old username when I need a quick > >> answer. If you can send a copy of your file to me I'll see what I can come up with. If you want to do that I'm at: xl can help at comcast period net Remove "can" and change the obvious. SignatureBiff Microsoft Excel MVP > Tyro, > [quoted text clipped - 40 lines] >> >> Thanks in advance and I am using an old username when I need a quick >> >> answer. Well, you couldn't seem to state a simple requirement. Don't blame me. You needed several prompts to even get you to the point where you could begin to express your needs. Thinking is required before attempting to solve a problem. I didn't make this requirement. If you can't think clearly and express yourself, then you will always have this problem. Don't blame me. Take remedial steps if you want to help yourself. I'm no jerk, BTW. Tyro > Tyro, > [quoted text clipped - 40 lines] >> >> Thanks in advance and I am using an old username when I need a quick >> >> answer. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.