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

bdav1216

New Member
Joined
Mar 19, 2016
Messages
24
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.
 

Some videos you may like

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
Joined
Aug 18, 2015
Messages
9,751
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
Joined
May 8, 2018
Messages
1,039
Office Version
365, 2010
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,124
Messages
5,466,828
Members
406,501
Latest member
TheoDoc

This Week's Hot Topics

Top