IF & AND Functions together
 

Dear Sirs,
How do i insert a "if" & " and" functions together. I have a list of
customers and a list of products ,which are being bought by customers at
diffrent point of times. I want to consolidate the information in the format
Customer "X" has bought so many units of products "A", Customer "Y" has
bought so many units of product "B" so on... The information that I receive
currently is in the format customerX / Material A/ Quantity / Bill no /
date,i.e. it is by material and date. So if a customer buys a material "A" 10
times during the month under 10 bills it is reflected 10 times .
Pl. oblige

You need a SUMIF, I think:
http://www.officearticles.com/excel/...ft_excel.h tm

But just in case you really need IF/AND:
http://www.officearticles.com/excel/...soft_excel.htm

*******************
~Anne Troy

www.OfficeArticles.com



"HARSH BAHAL" wrote in message
...
Dear Sirs,
How do i insert a "if" & " and" functions together. I have a list of
customers and a list of products ,which are being bought by customers at
diffrent point of times. I want to consolidate the information in the
format
Customer "X" has bought so many units of products "A", Customer "Y" has
bought so many units of product "B" so on... The information that I
receive
currently is in the format customerX / Material A/ Quantity / Bill no /
date,i.e. it is by material and date. So if a customer buys a material "A"
10
times during the month under 10 bills it is reflected 10 times .
Pl. oblige

=SUMPRODU CT(--($A$2:$A$100="Customer X"),--($B$2:$B$100="Product Y"))

--
HTH

Bob Phillips

"HARSH BAHAL" wrote in message
...
Dear Sirs,
How do i insert a "if" & " and" functions together. I have a list of
customers and a list of products ,which are being bought by customers at
diffrent point of times. I want to consolidate the information in the
format
Customer "X" has bought so many units of products "A", Customer "Y" has
bought so many units of product "B" so on... The information that I
receive
currently is in the format customerX / Material A/ Quantity / Bill no /
date,i.e. it is by material and date. So if a customer buys a material "A"
10
times during the month under 10 bills it is reflected 10 times .
Pl. oblige

Thanks a lot.I am going to try this, and hopefully it will help. However,
since I have around 50 customers and around 200 diffrent products, inserting
formulas individually will be long drawn process. Can't I specify a range
for my customers and product and d devise a formula. Would request you all
for helping me out on this.
Incidently pl enlighten me on the applications of the double dashes.
"Bob Phillips" wrote:

=SUMPRODU CT(--($A$2:$A$100="Customer X"),--($B$2:$B$100="Product Y"))

--
HTH

Bob Phillips

"HARSH BAHAL" wrote in message
...
Dear Sirs,
How do i insert a "if" & " and" functions together. I have a list of
customers and a list of products ,which are being bought by customers at
diffrent point of times. I want to consolidate the information in the
format
Customer "X" has bought so many units of products "A", Customer "Y" has
bought so many units of product "B" so on... The information that I
receive
currently is in the format customerX / Material A/ Quantity / Bill no /
date,i.e. it is by material and date. So if a customer buys a material "A"
10
times during the month under 10 bills it is reflected 10 times .
Pl. oblige

"HARSH BAHAL" wrote in message
...
Thanks a lot.I am going to try this, and hopefully it will help. However,
since I have around 50 customers and around 200 diffrent products,
inserting
formulas individually will be long drawn process. Can't I specify a range
for my customers and product and d devise a formula. Would request you all
for helping me out on this.

Put your customer names in A2, down, product names in B1 across, and then
put this in B2

=SUMPRODU CT(--($A$2:$A$100=$A2),--($B$2:$B$100=B$1))

and copy down and across


Incidently pl enlighten me on the applications of the double dashes.

See http://xldynamic.com/source/xld.SUMPRODUCT.html


"Bob Phillips" wrote:

=SUMPRODUCT(--($A$2:$A$100="Customer X"),--($B$2:$B$100="Product Y"))

--
HTH

Bob Phillips

"HARSH BAHAL" wrote in message
...
Dear Sirs,
How do i insert a "if" & " and" functions together. I have a list of
customers and a list of products ,which are being bought by customers
at
diffrent point of times. I want to consolidate the information in the
format
Customer "X" has bought so many units of products "A", Customer "Y"
has
bought so many units of product "B" so on... The information that I
receive
currently is in the format customerX / Material A/ Quantity / Bill
no /
date,i.e. it is by material and date. So if a customer buys a material
"A"
10
times during the month under 10 bills it is reflected 10 times .
Pl. oblige

Thanks a lot , I have learnt lot of other aspects also. Also I oresume it is
a typing error when you inserted a gap in " sumprod uct" I am puttting my
problem as follows :
In one sheet I have ( This is given, I cannot change this format)
customer code (A) material code (B) qty of material (C)
date
1 25 5
1
2 26 4
2
2 25 3
2
1 27 4
2
2 25 5
3
2 28 6
4
2 25 7
5
3 26 8
1
3 25 3
2
1 26 2
4
In another sheet I want to consolidate the data in the following format :
Material code(Down) Cutomer code (Across)
Quantity ( Down)

Would request you to kindly facilitae the devising of the formula.
"Bob Phillips" wrote:


"HARSH BAHAL" wrote in message
...
Thanks a lot.I am going to try this, and hopefully it will help. However,
since I have around 50 customers and around 200 diffrent products,
inserting
formulas individually will be long drawn process. Can't I specify a range
for my customers and product and d devise a formula. Would request you all
for helping me out on this.

Put your customer names in A2, down, product names in B1 across, and then
put this in B2

=SUMPRODU CT(--($A$2:$A$100=$A2),--($B$2:$B$100=B$1))

and copy down and across


Incidently pl enlighten me on the applications of the double dashes.

See http://xldynamic.com/source/xld.SUMPRODUCT.html


"Bob Phillips" wrote:

=SUMPRODUCT(--($A$2:$A$100="Customer X"),--($B$2:$B$100="Product Y"))

--
HTH

Bob Phillips

"HARSH BAHAL" wrote in message
...
Dear Sirs,
How do i insert a "if" & " and" functions together. I have a list of
customers and a list of products ,which are being bought by customers
at
diffrent point of times. I want to consolidate the information in the
format
Customer "X" has bought so many units of products "A", Customer "Y"
has
bought so many units of product "B" so on... The information that I
receive
currently is in the format customerX / Material A/ Quantity / Bill
no /
date,i.e. it is by material and date. So if a customer buys a material
"A"
10
times during the month under 10 bills it is reflected 10 times .
Pl. oblige

On your second sheet (Sheet2) in cell B2
=SUMPRODUCT(--(Sheet1!$A$2:$A$100=B$1),--(Sheet1!$B$2:$B$100=$A2),--(Sheet1!$C$2:$C$100))
Copy across and down to cover the range of your customers and products

--
Regards

Roger Govier


"HARSH BAHAL" wrote in message
...
Thanks a lot , I have learnt lot of other aspects also. Also I oresume it
is
a typing error when you inserted a gap in " sumprod uct" I am puttting my
problem as follows :
In one sheet I have ( This is given, I cannot change this format)
customer code (A) material code (B) qty of material
(C)
date
1 25 5
1
2 26 4
2
2 25 3
2
1 27 4
2
2 25 5
3
2 28 6
4
2 25 7
5
3 26 8
1
3 25 3
2
1 26 2
4
In another sheet I want to consolidate the data in the following format :
Material code(Down) Cutomer code (Across)
Quantity ( Down)

Would request you to kindly facilitae the devising of the formula.
"Bob Phillips" wrote:


"HARSH BAHAL" wrote in message
...
Thanks a lot.I am going to try this, and hopefully it will help.
However,
since I have around 50 customers and around 200 diffrent products,
inserting
formulas individually will be long drawn process. Can't I specify a
range
for my customers and product and d devise a formula. Would request you
all
for helping me out on this.

Put your customer names in A2, down, product names in B1 across, and then
put this in B2

=SUMPRODU CT(--($A$2:$A$100=$A2),--($B$2:$B$100=B$1))

and copy down and across


Incidently pl enlighten me on the applications of the double dashes.

See http://xldynamic.com/source/xld.SUMPRODUCT.html


"Bob Phillips" wrote:

=SUMPRODUCT(--($A$2:$A$100="Customer X"),--($B$2:$B$100="Product Y"))

--
HTH

Bob Phillips

"HARSH BAHAL" wrote in message
...
Dear Sirs,
How do i insert a "if" & " and" functions together. I have a list
of
customers and a list of products ,which are being bought by
customers
at
diffrent point of times. I want to consolidate the information in
the
format
Customer "X" has bought so many units of products "A", Customer
"Y"
has
bought so many units of product "B" so on... The information that I
receive
currently is in the format customerX / Material A/ Quantity /
Bill
no /
date,i.e. it is by material and date. So if a customer buys a
material
"A"
10
times during the month under 10 bills it is reflected 10 times .
Pl. oblige