constructing IF logical test based on text string stored in other cell

Hello! I know how to use text stored in quotes with an IF function to perform a logical test, such as =IF(A1="red",result if true, result if false). Is there a way to base the logical test on a text string stored in a different cell?

Sheet1 contains a large dataset. Each row contains a numerical score (0-100) in column A, one of 50 U.S. state abbreviations in column B (eg, IL, MS, WA), and several other criteria used in other parts of the spreadsheet. On the summary tab, I need to do some calculations (like median and standard deviation) on the scores on sheet1 based on text in several of the other columns, such as col A (state). Since MEDIAN and STDEV functions don't have "ifs" variants (like SUMIFS and AVERAGEIFS), I need to use IF statements to make the calculations work.

I want to construct one IF statement where the logical test is based on a text string contained in a cell from the same row so that when I pull down the formula to fill in, the rows below the first formula calculate based on the corresponding text (state abbreviation) in that row.

Thanks in advance for your input!

Hi daffodil11,

I'd rather not upload a sample workbook, but here are two tables showing the data and summary sheets as examples:

Data tab

A B C D
1 State Score County Date
2 MD 89 Ionia 9/6/2012
3 MD 81 Monroe 8/28/2012
4 AK 93 Washtenaw 8/27/2012
5 AK 91 Arenac 8/27/2012
6 AK 77 Madison 7/6/2012
7 ND 82 Madison 7/3/2012
8 TX 93 Jackson 7/5/2012
9 MD 77 Isabella 6/19/2012
10 VA 89 Tuscola 8/14/2012

Summary tab

A B
1 State Standard Deviation
2 AK #DIV/0! <-the formula in B2 is {=STDEV(IF(Data!B2:B10=Summary!B2,Data!C2:C10))} (Even confirmed with ctrl-shift-enter, the result is an error)
3 AL
4 AR
5 AS
6 AZ
7 CA
8 CO
9 CT
10 DC

I am trying to construct a formula in B2 that can be pulled down. In B3, I want to see the standard deviation for all scores in Alabama. In B4, the standard deviation for Arkansas, etc. This is a much larger dataset, and there are criteria other than states I want to use elsewhere. So for now I'm just trying to understand the concept of basing the logical test on a text string in a different column. Just naming the cell containing the text string (e.g. A2 or even &A2) doesn't seem to work with IF functions unless I'm missing some crucial syntax. Thanks so much again!

It certainly does work. I don't see what was causing the error when I attempted your solution earlier using a named range for the column containing the scores instead of the cell reference itself. But the IF statement seems to work fine now based on text contained in another cell.

By the way, sorry for perhaps including too much context in my original post. I fear it may have made the problem I was looking to solve appear broader than it was.

Thanks so much to all!