# Thread: Assistance with conditional formulas (Excel 2003)

1. ## Assistance with conditional formulas (Excel 2003)

Hi,

I need some assistance with some conditional formulas. There is 8 criteria to calculate. I have done them all but I am aware that array formulas are very processor intensive. The attachment is a sample sheet, but the real file contains over 10500 records. The arrays are working but very slow.

Is there variations of these formulas but NOT using arrays.
Many TX

2. ## Re: Assistance with conditional formulas (Excel 2003)

The following are all 'normal' (non-array) formulas (the array aspect is implicit in SUMPRODUCT):

In I6:

=SUMPRODUCT((G13:G151<>"White")/COUNTIF(B13:B151,B13:B151))

In I7:

=SUMPRODUCT((G13:G151<>"White")*(H13:H151="Female" )/COUNTIF(B13:B151,B13:B151))

In I8:

=SUMPRODUCT(L13:L151*(G13:G151<>"White")*(H13:H151 ="Female"))

In I9:

=SUMPRODUCT((G13:G151<>"White")*(I13:I151="YES")/COUNTIF(B13:B151,B13:B151))

In I10:

=SUMPRODUCT(L13:L151*(G13:G151<>"White")*(I13:I151 ="YES"))

3. ## Re: Assistance with conditional formulas (Excel 2003)

With some further experimentation I have noticed that it is only the {=SUM(1/COUNTIF(\$D\$13:\$D\$151,\$D\$13:\$D\$151))} formula that is REALLY slooooow. Is there maybe just a variation on this one?

Cheers

4. ## Re: Assistance with conditional formulas (Excel 2003)

TX Hans,

I'll try these out on the real file.

Many TX

5. ## Re: Assistance with conditional formulas (Excel 2003)

The alternative would be to use a custom VBA function, but I don't think this will be faster. Counting uniques in a large list is time-consuming.

You could turn off automatic recalculation, and only recalculate the sheet when you need up-to-date results.

Or you could migrate the data to a database (Access or SQL Server), and use totals queries.

7. ## Re: Assistance with conditional formulas (Excel 20

One of good methods to speed up multiple condition SUMs is:

Split out the multiple conditions into a column of helper formula, that return TRUE(1) or FALSE(0) for each row,

then, reference the helper column in a SUMIF or SUM formula

After added the helper columns at column N to column S, as per the attached file

Herein , the OP's formulae replaced by the advice formulae

1] Cell I3 =L152

Replaced by : =LOOKUP(9.99E+307,L:L)

2] Cell I4 =SUM(1/COUNTIF(\$D\$13:\$D\$151,\$D\$13:\$D\$151))

Replaced by : =SUM(Q13:Q151)

3] Cell I5 =SUMIF(G13:G151,"<>White",L13:L151)

Replaced by : =SUMIF(N13:N151,1,L13:L151)

4] Cell I6 =COUNTIF(G13:G151,"<>White")

Replaced by : =SUMIF(N13:N151,1)

5] Cell I7 =SUM(IF(G13:G151<>"White",IF(H13:H151="Female",1,0 ),0))

Replaced by : =SUM(R13:R151)

6] Cell I8 =SUM(IF(G13:G151<>"White",IF(H13:H151="Female",L13 :L151,0),0))

Replaced by : =SUMIF(R13:R151,1,L13:L151)

7] Cell I9 =SUM(IF(G13:G151<>"White",IF(I13:I151="YES",1,0),0 ))

Replaced by : =SUM(S13:S151)

8] Cell I10 =SUM(IF(G13:G151<>"White",IF(I13:I151="YES",L13:L1 51,0),0))

Replaced by : =SUMIF(S13:S151,1,L13:L151)

Regards
Bosco

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•