=SUM(IF(left(A19:A26,2)<"AD", IF(left(A19:A26,3)<"SUB",C19:C26,"")))
(still an array formula)

Or not an array formula:
=SUMPRODUCT(--(LEFT(A19:A26,2)<"ad"),--(LEFT(A19:A26,3)<"sub"),C19:C26)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Nancy Newburger wrote:

Hi,

I need an alternate formula, preferably simple (I am an Excel rookie) for
the following:

{=SUM(IF(A19:A26<"AD*", IF(A19:A26<"SUB*",C19:C26,"")))}

The above formula is not returning the correct results, all values in
C19:C26 are included when, based on the criteria some values should not be.
After reading posts regarding wildcards * ? in IF statements, I think
I understand that this formula will not work. If it will, could someone
please tell me what is wrong with it.

Otherwise, an alternate formula would be great.

Thanks for anyone's help in advance.

Nancy

--

Dave Peterson