IF & AND Functions together

[Guest]

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/sumif_using_multiple_criteria_in_microsoft_excel.htm

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

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

www.OfficeArticles.com

 

[Bob Phillips]

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

 

[Guest]

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]

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

 

[Guest]

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.

 

[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