IF & AND Functions together

[HARSH BAHAL]

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

[Anne Troy]

You need a SUMIF, I think:
http://www.officearticles.com/excel/...soft_excel.htm

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

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

www.OfficeArticles.com



"HARSH BAHAL" <[email protected]> wrote in message
news:[email protected]...
> 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

[Bob Phillips]

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

--
HTH

Bob Phillips

"HARSH BAHAL" <[email protected]> wrote in message
news:[email protected]...
> 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]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Bob Phillips]

"HARSH BAHAL" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>

[Roger Govier]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> > > news:[email protected]...
>> > > > 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
>> > >
>> > >
>> > >
>>
>>
>>