wildcard in IF function

[Nancy Newburger]

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]

=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 here:
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]

Dear Dave,
Thank you, thank you. The array formula worked perfectly.

 

[Dave Peterson]

I like the =sumproduct() version. It allows me to share the workbook with
others who don't have to know how to enter array formulas.

 

[joeu2004]

=SUMPRODUCT(--(LEFT(A19:A26,2)<>"ad"),--(LEFT(A19:A26,3)<>"sub"),C19:C26)

That's the best I could come up with, too. I was struggling to see if
there was a way to calculate LEFT() only once. I vaguely remember
seeing formulas of the form "expr<>{const1,const2}" meaning either
OR(expr<>"const1",expr<>"const2") or
AND(expr<>"const1",expr<>"const2"), I don't remember which. But I
could not get the "expr<>{...,...}" form to work as needed. Besides,
in my vague recollection, "const1" and "const2" were numbers, not
strings.

Anyway, is there no way to avoid calculating LEFT() more than once?

(Other than using a UDF.)

 

[Dave Peterson]

Not that I could think of--but that doesn't mean much. Maybe someone else will
jump in with a solution for both the 2 and 3 character strings.