SUMIF vs SUMPRODUCT

[Dave F]

The following returns 223,559.29:

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

The following returns 0:

=SUMIF(D2456,--(RIGHT(D2456,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

 

[Fred Smith]

Sumif does not support formulas for its criteria.

Regards,
Fred.

 

[Pete_UK]

Try it this way:

=SUM(IF(RIGHT(D2456,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

 

[Bernard Liengme]

Dave,
Should that be
=SUMPRODUCT(--(RIGHT(D2456,5)="37057"),--(Q2:Q456))
or
=SUMPRODUCT(--(RIGHT(D2456,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

 

[T. Valko]

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

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

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

In this case, --(RIGHT(D2456,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(D2456,1,Q2:Q456)
=SUMIF(D2456,0,Q2:Q456)

 

[RagDyer]

<<<"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)