Can a letter have a value in a cell?

[CHEFCGARDNER]

[TABLE="width: 500"]
<tbody>[TR]
[TD]POSITION[/TD]
[TD]POSITION[/TD]
[TD][/TD]
[TD][/TD]
[TD]TOTAL HOURS[/TD]
[/TR]
[TR]
[TD]AMC[/TD]
[TD]AMC[/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
IF POSITION AMC EQUALS 4 HOURS

HOW DO I GET EXCEL TO DO THIS FOR ME?

 

[Eric W]

Welcome to the Board.

I think you may be looking for something like:

Book1
	A	B	C	D
1	Position	Position		Total hours
2	AMC	AMC		8
Sheet4

Cell Formulas
Range	Formula
D2	=4*COUNTIF(A2:B2,"AMC")

This can be improved so that you have a table of different values for AMC and hours, but this would be a starting point.

 

[DanteAmor]

Maybe something like this
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:80.79px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:33px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">Position</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">Position</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">Total hours</td><td > </td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">Position</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">Value</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">AMC</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">AMC</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">8</td><td > </td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">AMC</td><td style="text-align:right; ">4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=VLOOKUP(A2,$F$2:$G$4,2,0)+VLOOKUP(B2,$F$2:$G$4,2,0)</td></tr></table></td></tr></table>

 

[CHEFCGARDNER]

Thanks Eric that was it So happy you could help me! but I have one more question. How would I write the formula if I wanted C2 to read PMC and also equal 4 in the same row? so now its looking for amc's and pmc's equaling 4?

 

[DanteAmor]

Hi @CHEFCGARDNER, If you allow me and with respect for Eric, I will answer.


Could be like this:

=4*SUM(COUNTIF(A2:C2,{"AMC";"PMC"}))

-------------------------------------------------------
I have a couple of doubts.


But what if PMC had a value other than 4?

Could be like this:

{=SUM(IFERROR(SUBSTITUTE(SUBSTITUTE(A2:C2,"AMC",4),"PMC",3)+0,0))}

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.

-------------------------------------------------------
Or if you are going to have more words?

Again I suggest a table of equivalences:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:80.79px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Position</td><td >Position</td><td >Position</td><td >Total hours</td><td > </td><td >Position</td><td >Value</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >AMC</td><td >BMC</td><td >PMC</td><td style="text-align:right; ">13</td><td > </td><td >AMC</td><td style="text-align:right; ">4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >PMC</td><td style="text-align:right; ">3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >BMC</td><td style="text-align:right; ">6</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Array Formula</td></tr><tr><td >D2</td><td >{=SUM(IF(ISNUMBER(SEARCH(A2:C2,F2:F4)),G2:G4))}</td></tr></table></td></tr></table>

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.

 

[Eric W]

Dante's adaptation of my original formula looks like it does what you want. He also has a very good point in that it might be a good idea to make a table with the values. It's much easier to change a table than a formula. And if someone comes along later, they don't have to wonder if the hardcoded 4 means hours, or points, or a column number, etc. Here's a non-array version of his D2 formula that reads the table:

=SUMPRODUCT(SUMIF(F2:F4,A2:C2,G2:G4))