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

=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

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

"Dave Peterson" wrote:

=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

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.

Nancy Newburger wrote:

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

"Dave Peterson" wrote:

=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


--

Dave Peterson

On Feb 24, 5:35*pm, Dave Peterson wrote:
=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.)

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.

joeu2004 wrote:

On Feb 24, 5:35 pm, Dave Peterson wrote:
=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