The following returns 223,559.29:

=SUMPRODUCT(--(RIGHT(D2:D456,5)="37057"),--(Q2:Q456))

The following returns 0:

=SUMIF(D2:D456,--(RIGHT(D2:D456,5)="37057"),Q2:Q456)

I would expect these two formulas to return the same value. What am I
doing wrong with the SUMIF formula (the value returned by SUMPRODUCT
appears to be correct.)

Thanks.

Dave

Sumif does not support formulas for its criteria.

Regards,
Fred.

"Dave F" wrote in message
...
The following returns 223,559.29:

=SUMPRODUCT(--(RIGHT(D2:D456,5)="37057"),--(Q2:Q456))

The following returns 0:

=SUMIF(D2:D456,--(RIGHT(D2:D456,5)="37057"),Q2:Q456)

I would expect these two formulas to return the same value. What am I
doing wrong with the SUMIF formula (the value returned by SUMPRODUCT
appears to be correct.)

Thanks.

Dave

SUMIF can't "manipulate" an *array* in any of it's agruments.

=SUMIF(D2:D456,--(RIGHT(D2:D456,5)="37057"),Q2:Q456)

In the formula you're attempting to manipulate the array D2:D456
using --(RIGHT(D2:D456,5)="37057").

In this case, --(RIGHT(D2:D456,5)="37057")

Evaluates only --(RIGHT(D2,5)="37057")

Which further evaluates to either 1 or 0 depending on what's in D2 so that
the criteria is actually either a 1 or 0:

=SUMIF(D2:D456,1,Q2:Q456)
=SUMIF(D2:D456,0,Q2:Q456)


--
Biff
Microsoft Excel MVP


"Fred Smith" wrote in message
...
Sumif does not support formulas for its criteria.

Regards,
Fred.

"Dave F" wrote in message
...
The following returns 223,559.29:

=SUMPRODUCT(--(RIGHT(D2:D456,5)="37057"),--(Q2:Q456))

The following returns 0:

=SUMIF(D2:D456,--(RIGHT(D2:D456,5)="37057"),Q2:Q456)

I would expect these two formulas to return the same value. What am I
doing wrong with the SUMIF formula (the value returned by SUMPRODUCT
appears to be correct.)

Thanks.

Dave

<<<"Sumif does not support formulas for its criteria."

Not really true!

If the formula returns a viable criteria, it will work within Sumif().

For example:

=Sumif(A1:A10,Sum(B2:B4),C1:C10)

Will work, where the sum formula equals a value in A1 to A10.

Or, if greater then the sum total:

=Sumif(A1:A10,""&Sum(B2:B4),C1:C10)
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Fred Smith" wrote in message
...
Sumif does not support formulas for its criteria.

Regards,
Fred.

"Dave F" wrote in message
...
The following returns 223,559.29:

=SUMPRODUCT(--(RIGHT(D2:D456,5)="37057"),--(Q2:Q456))

The following returns 0:

=SUMIF(D2:D456,--(RIGHT(D2:D456,5)="37057"),Q2:Q456)

I would expect these two formulas to return the same value. What am I
doing wrong with the SUMIF formula (the value returned by SUMPRODUCT
appears to be correct.)

Thanks.

Dave

Try it this way:

=SUM(IF(RIGHT(D2:D456,5)="37057",Q2:Q456))

This is an array formula, which means that once you have typed it in (or
subsequently edit it) you must use Ctrl-Shift-Enter (CSE) to commit it,
rather than the usual <Enter. If you do this correctly, then Excel will
wrap curly braces { } around the formula when viewed in the formula bar -
you must not type these yourself.

Note the similarity with the SUMPRODUCT formula, but with that you do not
need to use CSE.

Hope this helps.

Pete

"Dave F" wrote in message
...
The following returns 223,559.29:

=SUMPRODUCT(--(RIGHT(D2:D456,5)="37057"),--(Q2:Q456))

The following returns 0:

=SUMIF(D2:D456,--(RIGHT(D2:D456,5)="37057"),Q2:Q456)

I would expect these two formulas to return the same value. What am I
doing wrong with the SUMIF formula (the value returned by SUMPRODUCT
appears to be correct.)

Thanks.

Dave

Dave,
Should that be
=SUMPRODUCT(--(RIGHT(D2:D456,5)="37057"),--(Q2:Q456))
or
=SUMPRODUCT(--(RIGHT(D2:D456,5)="37057"), Q2:Q456)
The double negation is not needed on the Q range if they hold numbers
We use that to convert False/True to 0/1
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"Dave F" < wrote in message
...
The following returns 223,559.29:

=SUMPRODUCT(--(RIGHT(D2:D456,5)="37057"),--(Q2:Q456))

The following returns 0:

=SUMIF(D2:D456,--(RIGHT(D2:D456,5)="37057"),Q2:Q456)

I would expect these two formulas to return the same value. What am I
doing wrong with the SUMIF formula (the value returned by SUMPRODUCT
appears to be correct.)

Thanks.

Dave