# Excel Formula Assistance (If Statement w/lookup and SUM)

#### bdav1216

##### New Member
A few questions that I'm having some difficulty with, the 1st and 3rd questions are formula assistance.

1) Current formula (embedded in a text box): =ASVIEW!\$AR\$9
- I need to update this formula to SUM all values from AR9 through AR16. How would I go about updating this formula?

2) In Excel, is there a way to prevent multiple selections from a slicer? If not, are there any alternatives? Ideally, the user can select only one option, but I don't know of a better way to allow for a single selection from the slicer.

3) Need to write a formula to output the value found for the below scenario in Sheet3, A1:
If Sheet1 (Column A10) is = "ABC" THEN VLOOKUP TO Sheet2 -> Column W to find "ABC".
When "ABC" is matched, output the value found in Sheet2, Column X into Sheet3.

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Eric W

##### MrExcel MVP
1) Try:

=SUM(ASVIEW!\$AR\$9:\$AR\$16)

2) I don't know this one. 3) Maybe:

=IF(Sheet1!A10="ABC",VLOOKUP("ABC",Sheet2!\$W:\$X,2,0),"No match")

Or maybe just:

=IFERROR(VLOOKUP(Sheet1!A10,Sheet2!\$W:\$X,2,0),"No match")

#### kweaver

##### Well-known Member
Your #3 , if I'm reading it correctly: put this formula in the cell of Sheet3 where you want the answer.

=IF(Sheet1!A10="ABC",Vlookup("ABC",Sheet2!W1:X100,2,false),"")

You didn't say what you wanted in Sheet3 if ABC wasn't found, so I put a blank (i.e., "")

If this isn't what you meant or isn't sufficient to adjust to your need, you'll need to post an example.