i am in search of Formula / Function in EXCEL-2003

[Nimish Shah]

Dear Sir,

I am in search of a formula to fill up the cells of "Price per each" column ?

Data Available
Farmer Product Price Per Each Farmer Product Price Per Each

Tom Apple Tom Apple 10
Harry Apple Tom Mango 15
Harry Banana Tom Banana 5
Tom Apple Harry Apple 20
Harry Mango Harry Mango 30
Harry Apple Harry Banana 7
Tom Mango
Harry Mango
Tom Banana
Tom Mango
Tom Banana
Tom Apple
Harry Apple
Tom Mango

Regards,

Nimish

 

[yshridhar]

Hi Nimish
Say if your data table is in A1:C7;
Try it in C9
C9 =INDEX($A$1:$C$7, MATCH(1,(($A$1:$A$7=A9)*($B$1:$B$7=B9)),0),
MATCH("Cost",$A$1:$C$1,))
it is an array formula. you have to use ctrl+shift+enter not just enter
A9= Name
B9 = Product
with regards
Sridhar

 

[Nimish Shah]

Hi Sridhar,

I have tried the formula but not working, showing as #N/A.
I could not understand: match("cost",$a$1:$c$1,)).
I have never used the ARRAY formula.

Please help.

Regards,

Nimish

 

[yshridhar]

Array formula has to be enetered by ctrl+shift+enter. Not just by pressing
enter.
a1 = name
b1 = product
c1 = cost
change the column heads according to your data.
You have to enter the formula by pressing ctrl+shift+enter
with regards
Sridhar

 

[yshridhar]

The following is your data
Name Product Cost
Tom Banana 10
Tom Mango 20
Tom Apple 30
Harry Banana 40
Harry Mango 50
Harry Apple 60

a9 = Tom
b9 = apple
in c9=INDEX($A$1:$C$7,
MATCH(1,(($A$1:$A$7=A9)*($B$1:$B$7=B9)),0),MATCH("Cost",$A$1:$C$1,))
enter the formula in c9. Press F2 and then use ctrl(control
key)+shift+enter.
change the column heads accordingly. "cost" is the column head for the
Price of your data.
with regards
sridhar

 

[yshridhar]

The following is the data
Name Product Cost
Tom Banana 10
Tom Mango 20
Tom Apple 30
Harry Banana 40
Harry Mango 50
Harry Apple 60

A9 = Tom
B9 = Apple
C9 =INDEX($A$1:$C$7,
MATCH(1,(($A$1:$A$7=A9)*($B$1:$B$7=B9)),0),MATCH("Cost",$A$1:$C$1,))
change the column heads and range according to your data.
You can find help about array formula in excel.
copy down the formula in C9 and press ctrl+shift+enter.
with regards
Sridhar

 

[Nimish Shah]

Sridhar,

Thank you so much. It woorks very well. i had forgot to name C1 as cost.
i did do the ctrl+shift+enter.

Regards,
Nimish

 

[yshridhar]

Nimish
You can try this simple formula
C10 = SUMPRODUCT(--($A$2:$A$7=A10)*--($B$2:$B$7=B10),C2:C7)
A10 = Tom
B10 = Apple
a2:a7 = farmer names
b2:b7 = product names
c2 : c7 = price
With regards
Sridhar

 

[yshridhar]

Thanks. It solved your problem. But the sumproduct version is simple than
the array formula.
with regards
Sridhar

 

[Nimish Shah]

Sridhar,

Yes i also learnt this style of usign the sumproduct formula also. Thanks a
lot and enjoy the festive holidays of year ending.

Regards,
Nimish

 

[Nimish Shah]

Hi Sridhar,

I am in search of a sot of reverse formula. Example is given below. Please
help.

Farmer COST-Apple COST-Banana COST-Mango
Tom ??? ??? ???
Harry ??? ??? ???

Data Available
Farmer Product COST
Tom Apple 10
Harry Apple 20
Harry Banana 7
Tom Apple 10
Harry Mango 30
Harry Apple 20
Tom Mango 15
Harry Mango 30
Tom Banana 5
Tom Mango 15

Regards,

Nimish

 

[yshridhar]

Hi Nimish
I hope by this time you have sorted the problem.
The same sumproduct will work.
Try this
b12=SUMPRODUCT(--($A$2:$A$7=$A12)*--($B$2:$B$7=B$11),$C$2:$C$7)
a12 = Tom
b11= apple (column heads "farmer- A, apple - B, Banana - C, Mango - D)
i replaced cost-apple with apple
a2:c7 is your data.
Just copy down the formula
with regards
Sridhar

 

[Nimish Shah]

Sridhar,

Thank you. This i could do with the earlier formula and also i learnt the
PIVOT TABLE, very useful.

Regards,

Nimish

 

[yshridhar]

You are welcome. Thanks for feedback
with regards
Sridhar

 

[Nimish Shah]

price per each nos
a b c d a c f b
2 3 4 6 5 6 7 9
5 2 3 8 4 9 6 5
6 3 4 2 9 8 3 2


a=84
b=43
c=83
d=0
f=0

I want a formula which will automatically calculate value of "a" after
searching "a" in "Price per each" block and then in "nos" block and will
sumproduct the said column and give result like "84", "43", "83" etc

 

[T. Valko]

With these ranges of data given defined names of tbl1 and tbl2:

........tbl1...........................tbl2.........
price per each nos
a b c d a c f b
2 3 4 6 5 6 7 9
5 2 3 8 4 9 6 5
6 3 4 2 9 8 3 2

A1:A5 = a, b, c, d, f

Enter this formula in B1 and copy down to B5:

=IF(COUNTIF(tbl1,A1)+COUNTIF(tbl2,A1)<2,0,SUMPRODUCT(INDEX(tbl1,,MATCH(A1,INDEX(tbl1,1,),0)),INDEX(tbl2,,MATCH(A1,INDEX(tbl2,1,),0))))

 

[Nimish Shah]

Dear Sir,

I have tried this formula but it is not working giving #NAME?.
In this formula defining tbl1 and tbl2 is not clear to me.

Please help to solve this formula

Nimish

 

[T. Valko]

Assume this data is in the range A14 -

a b c d
2 3 4 6
5 2 3 8
6 3 4 2

Select the range A14
In the name box, that little box directly above column A, type in tbl1 then
hit ENTER

Assume this data is in the range H1:K4 -

a c f b
5 6 7 9
4 9 6 5
9 8 3 2

Select the range H1:K4
In the name box, that little box directly above column A, type in tbl2 then
hit ENTER

A10:A13 = A, B, C, D

Enter this formula in B10 and copy down to B13:

=IF(COUNTIF(tbl1,A10)+COUNTIF(tbl2,A10)<2,0,SUMPRODUCT(INDEX(tbl1,,MATCH(A10,INDEX(tbl1,1,),0)),INDEX(tbl2,,MATCH(A10,INDEX(tbl2,1,),0))))