Sum help

  • Thread starter Thread starter David Stricklen
  • Start date Start date
D

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?
 
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
 
Back
Top