Sum help

[David Stricklen]

I have a spreadsheet that has 10,000 Entries in one column & I want it to sum everything assuming it follows two conditions. The problem is that some cells in the column have words and letters, which I think confuses excel, and gives me and error. I then tried this array formula: =IF(ISERROR(SUM((Detail!$A$6:$A$42=B122)*(Detail!$E$6:$E$42=D122)*(Detail!$O$6:$O$42))),"",SUM((Detail!$A$6:$A$42=B122)*(Detail!$E$6:$E$42=D122)*(Detail!$O$6:$O$42))), which works if I manually delete every cell that has words and numbers. Is there anyway around this?

 

[PCLIVE]

Is your formula returning an error? If so, what is it?
Which column may contain words and letters (or I'm assuming you meant words and numbers)? Is it the sum column? I don't really think it matters anyway, though. However, if there are errors in your ranges, then your formula will probably always produce an error.

I think you will want to use a SUMPRODUCT function.

=SUMPRODUCT(--(Detail!$A$6:$A$42=B122),--(Detail!$E$6:$E$42=D122),Detail!$O$6:$O$42)

HTH,
Paul