 |
 | 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 / New Users / January 2006Can I combine "if" and "and" formulas?
I have information in the following cells: A1 = customer name B1 = product type (software, services, training, support, etc.) C1 = sale price D1 = a percentage of the sale that represents the support component I would like to look at A1 and if the value is: customer a, or customer b, or customer c AND if cell B1 = software, or support, then multiply the number in C1 by the percentage that's captured in D1 Can this be done? Thanks in advance. Robert Hi Robert I made a named range in H1:H3 called Customers and filled it with customer a, customer b etc. I made a named range in I1:I2 called products and filled it with software and support In cell E1 =(COUNTIF(customers,A1)*COUNTIF(products,B1)*D1)*C1 If you make the named ranges dynamic, then you can enter any number of customers into the range. Similarly with products  SignatureRegards Roger Govier >I have information in the following cells: > [quoted text clipped - 11 lines] > Thanks in advance. > Robert Thanks Roger, I'm not sure if what you're talking about will solve my need, so let me try and be more specific (sorry for not being more so the first time): I want to multiply the sale price by the support percentage ONLY when: - 4 or 5 SPECIFIC customers out of the 150 unique customers I have are identified in column A AND - when "software" or "support" out of the 11 different sale types are specified in column B It might read like this: I have 1000 orders that is detailed to the line item level in a spreadsheet. I have 150 unique customer names in column A. I have 11 unique order types column B. 4 or 5 of my customers bundle the first year of support in with the initial product purchase (therefore, there isn't a separate and distinct line item for first year of support). I want to measure my support potential. To do that, I want to add all the line items that are identified as "1st year", PLUS a percentage of the orders that are for those 4 or 5 customers. In the attached sample, I want to multiply the sale price times the percentage for Me, You, and Us when the Product Type is either Software or Support. What do you think? >Hi Robert > [quoted text clipped - 7 lines] >If you make the named ranges dynamic, then you can enter any number of >customers into the range. Similarly with products Try Rogers suggestion, I think it does what you want. SignatureHTH RP (remove nothere from the email address if mailing direct) > Thanks Roger, > [quoted text clipped - 38 lines] > >If you make the named ranges dynamic, then you can enter any number of > >customers into the range. Similarly with products Hi Robert The formula I gave you will do just that. It is saying look at the name in A1, and see if it occurs within the list customers. If it does it will return a 1, if not a 0. It then looks a the entry in B1 and looks it if occurs within the list products. If it does it will return a 1, if not a 0. It then multiplies this by the percentage in D1 Finally, this value is multiplies by the value in C1. So if customer is true, value =1 If product is true value =1 1 * 1 * 10% =10% C1 * 10% is the result in column E If either customer returns False, or product returns false, then 0 * 1 * 10% = 0% 1 * 0 * 10% = 0% so there will be no value in Column E Extend the named range Customers to cells H1:H5 and enter those 5 customers names in the 5 cells. Copy the formula down column E The total of column E is what you want. If your not sure how to create a named range. Mark the range e.g. H1:H5 in the white pane to the left of column A and above row 1 type Customers. Hit return. Repeat with I1:I2 and call it Products I can't see any attached sample, and attaching files to postings in these NG's is not allowed. It would be possible to do the whole sum with a SUMPRODUCT formula, but its just after midnight here in the UK and I'm off for some sleep. You can get your answer by copying my formula down column E and summing it, but someone else may come up with a single Sumproduct for you. Cheers. SignatureRegards Roger Govier > Thanks Roger, > [quoted text clipped - 38 lines] >>If you make the named ranges dynamic, then you can enter any number of >>customers into the range. Similarly with products I think I understand now. I didn't get that you had listed the specific customers in a1. I think I follow you now. Thanks! >Hi Robert > [quoted text clipped - 35 lines] > >Cheers. I couldn't attache the sample, so I'll cut/paste it in the message below: =========================================== I'm not sure if what you're talking about will solve my need, so let me try and be more specific (sorry for not being more so the first time): I want to multiply the sale price by the support percentage ONLY when: - 4 or 5 SPECIFIC customers out of the 150 unique customers I have are identified in column A AND - when "software" or "support" out of the 11 different sale types are specified in column B It might read like this: I have 1000 orders that is detailed to the line item level in a spreadsheet. I have 150 unique customer names in column A. I have 11 unique order types column B. 4 or 5 of my customers bundle the first year of support in with the initial product purchase (therefore, there isn't a separate and distinct line item for first year of support). I want to measure my support potential. To do that, I want to add all the line items that are identified as "1st year", PLUS a percentage of the orders that are for those 4 or 5 customers. In the attached sample, I want to multiply the sale price times the percentage for Me, You, and Us when the Product Type is either Software or Support. ============================================ Customer Name Product Type Sale Price Support Percentage Support Component Me Training $100 0.2 You Software $100 0.2 Us Support $100 0.2 Them Travel $100 0 Everyone Else Media $100 0 Nobody Software $100 0 All Software $100 0 Me Support $100 0.2 You Travel $100 0.2 Us Training $100 0.2 Them Software $100 0 Everyone Else Software $100 0 Nobody Support $100 0 All Travel $100 0 Me Media $100 0.2 You Software $100 0.2 Us Software $100 0.2 Them Support $100 0 Everyone Else Travel $100 0 Nobody Training $100 0 All Software $100 0 Me Support $100 0.2 You Travel $100 0.2 Us Media $100 0.2 Them Software $100 0 Everyone Else Software $100 0 Nobody Support $100 0 All Travel $100 0 What do you think?On Sun, 8 Jan 2006 22:37:21 -0000, "Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote: >Hi Robert > [quoted text clipped - 7 lines] >If you make the named ranges dynamic, then you can enter any number of >customers into the range. Similarly with products > I have information in the following cells: > A1 = customer name [quoted text clipped - 4 lines] > customer b, or customer c AND if cell B1 = software, or support, then > multiply the number in C1 by the percentage that's captured in D1 Does the following meet your needs? =IF(AND(OR(A1="a",A1="b",A1="c"),OR(B1="software",B1="support")), C1*D1, "") Note: You fail to say what to do if those conditions are not met. I chose a blank result (""). You might want something different. > Can this be done? > > Thanks in advance. > Robert |
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.