Can I combine "if" and "and" formulas?

[darkwing_duck]

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

 

[Roger Govier]

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

 

[Guest]

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

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.

 

[darkwing_duck]

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?

 

[darkwing_duck]

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"

 

[Bob Phillips]

Try Rogers suggestion, I think it does what you want.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Roger Govier]

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.

 

[darkwing_duck]

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!