Formula to return specified value based on criteria

[adriam25]

Hello guys, i need a lil help. I'm trying to do a formula that shows a value based on text.
for ex: if cell a1 contains word "three" then show value 3

is there any way to do that. thanks

[FDibbins]

Maybe this...
=if(A1="Three",3,"")

If you have more than just a few criteria, it will probably be better to create a small table with the criteria/values, then use VLOOKUP()

[adriam25]

thanks... its just a little table that I'm trying to create... that will work perfectly.. thanks a lot.

[FDibbins]

OK cool, glad to help

If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

[adriam25]

OK!!!. i think i need a lil more help. i don't ask the correct question. lol.
its a count formula that i need.
for ex.
if a1 contains "three" then count 3 but if contains "two" count 2

thats what I'm trying to do.


sorry for my ignorance.

[adriam25]

IM DOING THIS =IF(A1:A10="THREE",3"")
the formula gives me a value error

[FDibbins]

I think this is probably a bit more complex that you are trying to make it. What exactly are you doing? (sometimes, when trying to simplify an example, it ends up missing the point)

If it's just those 2, then maybe...

=IF(countif(A1:A10,"Three")=3,3,IF(countif(A1:A10,"Two")=2,2,""))

[adriam25]

OK, I'm trying to do a counter of lines of business sold, saying that TP is equal to 3 lob's, DP is 2 and SP is 1
i'm trying to get something we call "RGU" which is the percentage of the total amount of LOB divided by the total amount of calls that we get. i work for a call center.

the formula you gave me it doesn't give me any error but i doesn't show any value . i did =IF(countif(A1:A10,"Three")=3,3,&IF(countif(A1:A10,"Two")=2,2,""))

then it gives me a crazy error that close my excel. some how.

[FDibbins]

you have an & sign in there that should not be there
=IF(countif(A1:A10,"Three")=3,3,&IF(countif(A1:A10,"Two")=2,2,""))

[FDibbins]

hmm wait. So you are trying to get the sum of all TP's + all DP's + all SP's?

If so, then try this...
=SUM(COUNTIF(A1:A1000,{"TP","DP",SP}))

[adriam25]

got it. but were should i put the value of does occurrences TP= 3, DP=2 and SP=1? at the end of the occurrence right?

[FDibbins]

LOL sorry, forgot about that part

=SUM(COUNTIF(A1:A1000,"TP")*3,COUNTIF(A1:A1000,"DP")*2,COUNTIF(A1:A1000,SP))

[adriam25]

AWESOME!!! Works perfectly. thanks a lot!!!!

the last part of the formula was missing the 'sp')*1 but i fix it already. wow. that was a pain in the pain. lol. i didn't know how to formulate that. thanks.!!

[FDibbins]

oops sorry for the missing "", glad you caught that (my excuse is that its late here lol)

If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)