If then help!

jtramell

New Member
Joined
Mar 18, 2009
Messages
41
I am trying to write a formula that will look at H14:H20 & R14:R20 to look for an "X", if there is an X in one of those boxes I want it to copy the number in E14:E20 & O14:O20 in the corresponding row. I had it once but think that I stumbled on it by accident and when IT upgraded the software to 2003 from 2000 it got "lost in translation" and now that is the only formula that does not work. Please help, :confused:

Thanks
John
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
Welcome to MrExcel board...

so what is the formula you have that does not work?
 

jtramell

New Member
Joined
Mar 18, 2009
Messages
41
Its gone and cant find one in the editor that will work for me. I am trying to use =IF(H14:H20,R14:R20="X",E14:E20,O14:O20,0) It is not working though and I can not figure out what the formula was before that worked and the backup was corrupted in the excel update. Thanks
 

jtramell

New Member
Joined
Mar 18, 2009
Messages
41

ADVERTISEMENT

I want to copy the data to a A32
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
try editing the formula and then instead of typing enter key to exit, thype enter whilst holding ctrl+shift
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458

ADVERTISEMENT

maybe this?
=INDEX(E14:E20,MATCH("X",H14:H20,0))
 

jtramell

New Member
Joined
Mar 18, 2009
Messages
41
It's telling me too many arguments. I have tried nesting it with this formula but it is very long.

=IF(H14="X",E14,IF(H15="X",E15,IF(H16="X",E16,IF( ))))so and and so on.

would VLOOKUP be another possible way to go? I have never used this before.

Thanks
 

jtramell

New Member
Joined
Mar 18, 2009
Messages
41
I think I fixed it with a work around. I placed the following formulas to accomplish this.

=sumproduct((E14:E20)*(H14:H20="X")) in one hidden cell
=sumproduct((O14:O20)*(R14:R20="X")) in another hidden cell
then I just auto sum those two hidden cells in A32 for my information

Its a long way to go for such simple information. Thanks for your assitance though with this matter.

John
 

jbvinny

Active Member
Joined
Nov 21, 2007
Messages
274
I think =VLOOKUP() would have a problem with your table having columns in between that are not part of your table.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top