IF statements

[jon2249]

Hey guys!



I am trying to go from L to A in the Grey shaded area to find "1" OR "1A". If there is an x or any other number in the column it will go until it shows "1" or "1A". When it shows 1 in the cell, It will go up to the white shaded to find the price. The price is then posted in the yellow cells.

I do apologize if this has been answered but I am uncertain if it is even an IF statement to complete this task. Please let me know if this is possible.

Thanks guys.

 

[VBACO]

How about
=SUMIF(B14:B25,"<>x",B1:B12)

Or if it has to be "1" or "1A" and nothing else then
=SUM(SUMIF(B15:B26,1,B1:B12),SUMIF(B15:B26,"1A",B1:B12))

 

[jon2249]

How about
=SUMIF(B14:B25,"<>x",B1:B12)

Hey Thanks for the reply,

What that did was find was take the sum of all the cells that had 1 in it. I am trying to go to the lowest value that has 1.

Different numbers will be in the grey area but as long as the value has a 1 or 1A the value should be posted of the Lowest price.

For instance the 2nd column the lowest 1 value is found in I. The yellow area is then posted with the value of I found in the table posted above.

 

[VBACO]

Sorry, I misunderstood your question. You may want to have a look at http://www.mrexcel.com/articles/CSE-array-formulas-excel.php

 

[VBACO]

You should read that post. But if you really just want the quick answer then

=MIN(IF(B16:B27=1,B1:B12))
Ctrl+Shift+Enter

 

[jon2249]

You should read that post. But if you really just want the quick answer then

=MIN(IF(B16:B27=1,B1:B12))
Ctrl+Shift+Enter

Thanks VBACO!

it works great! The link does not show how i can check for "1" and "1A" at the same time is there a way to test 1 and 1A at the same time?


For Example: Column C

 

[VBACO]

There is probably a better way but using a nested if seems to work

=MIN(IF(B16:B27=1,B1:B12,IF(B16:B27="1A",B1:B12)))
Ctrl+Shift+Enter

 

[jon2249]

There is probably a better way but using a nested if seems to work

=MIN(IF(B16:B27=1,B1:B12,IF(B16:B27="1A",B1:B12)))
Ctrl+Shift+Enter

you are a life saver! Thanks Vbaco!!!