Nested If statements that dont stop reading when condition is met

**haitham1984** · 10-31-2008, 07:48 AM

Firstly, I have to warn you this might take time.

I have the following table on EXCEL:

A B C
1 UNIT_NAME Total_Capacity #_of_units
2
3 UNIT1 38,090 0
4 UNIT2 48,012 1
5 UNIT3 59,123 0
6 UNIT4 71,027 0
7 UNIT5 100,390 3

To minimze the work on this user of this program, I promopt the user to fill in column C only, where he selects the number of air-conditioning units he wants. After the user enters these values, I call them on the next page where I created a receipt for the customer, and the receipt shows the units he purchashed.

However the actual list of units that are avaliable are not only the 5 that I've listed, there are 18 units all together, so the table above is MUCH longer. The problem I am having is with the receipt, where I call the number of units the user purchased.

I used nested if statements (which are limited to 7), below is an example of the code:

=IF(C3>0,A3,IF(C4>0,A4,IF(C5>0,A5,IF(C6>0,A6,IF(C7>0,A7,IF(C8>0,A8,IF(C8>0,A8,IF(C9>0,A9.........etc........))))))))

but the problem is (refer to table above) that when the if statement reads that C4 is positive, it take that number and stops reading for the next positive values.

My Question:

How can I create a code that stores the first positive value (probably in a variable) from the list (in the example above, C4) and continues to read and store for the next positive value (C7)....and continues to do so until the end of the 18-value (# of units) list?

Thank you for your time!

**arthurbr** · 10-31-2008, 07:50 AM

Hi and welcome to the board,
please read forum rules and change your title accordingly
Thx

**arthurbr** · 10-31-2008, 08:11 AM

Thx for changing your title

**ratcat** · 10-31-2008, 08:32 AM

G'day

There is an array formula you can use (see attachment).

If the unit name aren't unique, you will have to apply the array formula across the 3 columns. By doing this you will increase the size of the workbook and slow down the calculation of the workbook especially if your computer not that A1.

Please after editing the array formula confirm with Ctrl Shift Enter. The other columns has a Vlookup formula return the value of the Unit name.

HTH

**DonkeyOte** · 10-31-2008, 08:36 AM

maybe I'm missing the point but it sounds like you just want to sum the total number of units sold in one cell on your invoice ?

if that's the case just use =SUM(Data!$C$3:$C$20)

where Data is your Unit_Name / No. of Units sheet