You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Excel complicated formula condition

Advertisement

I am trying to created a summary result of other variables in my worksheet.

There are 5 variables (A, B, C, D, E) and 2 sheets (P1 and P2) that are in play.

Summary is listed vertically.

I can get this formula right.

=IF(P1!E9>0,P1!B9,IF(P1!E10>0,P1!B10,IF(P!!E11>0,P1!B11, IF(P1!E12>0,P1!B12,IF(P1!E13>0,P1!B13,"")))))

To give me A in the top cell. I than make the above formula shorter (Drop the first P1!E9 part) to get B in the next cell below and so on to get all the way to E.

Works great as long as there is always quantities in all variables (A-E) I get a vertical list of

A

B

C

D

E

But if A is not >0 I get

B

B

C

D

E

And if C is not >0 I get

B

B

D

D

E

How do I make it for the example of no A or C have the result of

B

D

E

only?

What I need is

1st Cell: Formula works

2nd cell: if 1st cell is blank or has B then do not show B

3rd cell: if 1st or 2nd cell is blank or has C then do not show C

4th cell: if 1st, 2nd or 3rd cell is blank or has D then do not show D

5th cell: if 1st, 2nd, 3rd or 4th cell is blank or has E then do not show E

I'm pretty sure this ARRAY formula will do what you want:

=INDEX($B$9:$B$13, SMALL(IF($P$9:$P$13>0, ROW($P$9:$P$13)-8, ""), ROWS($A$1:$A1)))

Enter that formula into the first cell and confirm it by pressing CTRL-SHIFT-ENTER, and the "B" should appear if that's the first row with a value > 0. You will know the array is active in that cell because you can see curly braces { } appear around your formula.

If you get a #VALUE error, you probably don't see the braces { } either, so try entering the formula again. CTRL-SHIFT-ENTER feels weird until you're used to doing it instead of just ENTER.

Once the first value appears, copy that cell down. You'll get #NUM errors when you run out of results. You can suppress the NUM errors by adding an IFERROR() around the formula:

=IFERROR(INDEX($B$9:$B$13, SMALL(IF($P$9:$P$13>0, ROW($P$9:$P$13)-8, ""), ROWS($A$1:$A1))), "")

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files

Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files
=====================
I have been offering free assistance as an Excel aid on many web sites for many years:
(http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)**Education/Credentials**

Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition**Awards and Honors**

Microsoft Excel MVP 2010