| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
i am in search of Formula / Function in EXCEL-2003
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
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" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
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" wrote: 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" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
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 "Nimish Shah" wrote: 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" wrote: 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" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
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 "Nimish Shah" wrote: 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" wrote: 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" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
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 "yshridhar" wrote: 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 "Nimish Shah" wrote: 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" wrote: 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" wrote: 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
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" wrote: 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 "Nimish Shah" wrote: 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" wrote: 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" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
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 "Nimish Shah" wrote: 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" wrote: 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" wrote: 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
Thanks. It solved your problem. But the sumproduct version is simple than
the array formula. with regards Sridhar "Nimish Shah" wrote: 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" wrote: 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 "Nimish Shah" wrote: 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" wrote: 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" wrote: 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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
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 "yshridhar" wrote: Thanks. It solved your problem. But the sumproduct version is simple than the array formula. with regards Sridhar "Nimish Shah" wrote: 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" wrote: 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 "Nimish Shah" wrote: 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" wrote: 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" wrote: 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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
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" wrote: 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 "Nimish Shah" wrote: 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" wrote: 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" wrote: 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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
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" wrote: 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" wrote: 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 "Nimish Shah" wrote: 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" wrote: 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" wrote: 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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
Sridhar,
Thank you. This i could do with the earlier formula and also i learnt the PIVOT TABLE, very useful. Regards, Nimish "yshridhar" wrote: 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" wrote: 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" wrote: 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 "Nimish Shah" wrote: 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" wrote: 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" wrote: 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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
You are welcome. Thanks for feedback
with regards Sridhar "Nimish Shah" wrote: Sridhar, Thank you. This i could do with the earlier formula and also i learnt the PIVOT TABLE, very useful. Regards, Nimish "yshridhar" wrote: 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" wrote: 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" wrote: 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 "Nimish Shah" wrote: 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" wrote: 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" wrote: 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 |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
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 |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
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,SUMPRODU CT(INDEX(tbl1,,MATCH(A1,INDEX(tbl1,1,),0)),INDEX(t bl2,,MATCH(A1,INDEX(tbl2,1,),0)))) -- Biff Microsoft Excel MVP "Nimish Shah" wrote in message ... 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 |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
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" wrote: 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,SUMPRODU CT(INDEX(tbl1,,MATCH(A1,INDEX(tbl1,1,),0)),INDEX(t bl2,,MATCH(A1,INDEX(tbl2,1,),0)))) -- Biff Microsoft Excel MVP "Nimish Shah" wrote in message ... 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 |
|
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
i am in search of Formula / Function in EXCEL-2003
Assume this data is in the range A1:D4 -
a b c d 2 3 4 6 5 2 3 8 6 3 4 2 Select the range A1:D4 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,SUMPRO DUCT(INDEX(tbl1,,MATCH(A10,INDEX(tbl1,1,),0)),INDE X(tbl2,,MATCH(A10,INDEX(tbl2,1,),0)))) -- Biff Microsoft Excel MVP "Nimish Shah" wrote in message ... 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" wrote: 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,SUMPRODU CT(INDEX(tbl1,,MATCH(A1,INDEX(tbl1,1,),0)),INDEX(t bl2,,MATCH(A1,INDEX(tbl2,1,),0)))) -- Biff Microsoft Excel MVP "Nimish Shah" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel Search Function | Excel Discussion (Misc queries) | |||
| Excel search function | Excel Discussion (Misc queries) | |||
| In Excel 2003 I must do a file search to fine my files | Excel Discussion (Misc queries) | |||
| search box in excel 2003? | Excel Worksheet Functions | |||
| Excel 2003 SP 1 does not search for files on a removable HDD | Excel Discussion (Misc queries) | |||
Linear Mode
