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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,309
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
927
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,089,765
Messages
5,410,290
Members
403,308
Latest member
JasonTheGreat

This Week's Hot Topics

Top