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