Long Array Formula Problem

**tnederlof** · 08-20-2009, 01:49 PM

Tried it and it returns zero so no such luck there. Basically I have columns DJ-DT on a micro sheet. DJ has 401(k)'s, DK has 401(a)'s, etc. I want to count a row if even just one of the 11 cells has something in it. Maybe there is an easier way and I just complicated it?

**NBVC** · 08-20-2009, 01:52 PM

Thought your were checking if any one row contains all conditions...

try then:

=SUMPRODUCT((micro!DJ3:DJ8000="401(k)")+(micro!DK3:DK8000="401(a)")+(micro!DL3:DL8000="403(b)"),--(micro!DM3:DM8000="457")+(micro!DN3:DN8000="Roth 401(k)/Roth 403(b)")+(micro!DO3:DO8000="Money Purchase")+(micro!DP3:DP8000="Profit Sharing/ESOP")+(micro!DQ3:DQ8000="Non-Qualified Deferred Comp")+(micro!DR3:DR8000="SEP")+(micro!DS3:DS8000="SIMPLE IRA")+(micro!DT3:DT8000<=""))

**tnederlof** · 08-20-2009, 02:00 PM

I tried that and I got a number that is to high. I think its close. Why is it in two arrays? Sorry I am new to this complexity of formulas. I see -- at one point in the formula. Should these be put before each column or not at all?

**NBVC** · 08-20-2009, 02:08 PM

I messed up the last condition:

=SUMPRODUCT((micro!DJ3:DJ8000="401(k)")+(micro!DK3:DK8000="401(a)")+(micro!DL3:DL8000="403(b)"),--(micro!DM3:DM8000="457")+(micro!DN3:DN8000="Roth 401(k)/Roth 403(b)")+(micro!DO3:DO8000="Money Purchase")+(micro!DP3:DP8000="Profit Sharing/ESOP")+(micro!DQ3:DQ8000="Non-Qualified Deferred Comp")+(micro!DR3:DR8000="SEP")+(micro!DS3:DS8000="SIMPLE IRA")+(micro!DT3:DT8000<>""))

**tnederlof** · 08-20-2009, 02:23 PM

I really appreciate your help. Now the number is to low... 911 when it should be 2522 (when I do it manually). Any ideas? Can you explain what your formula is doing and maybe if its doing something else I can tell you what I need it to do more clearly?