SUMIF vs SUMPRODUCT

  • Thread starter Thread starter Dave F
  • Start date Start date
D

Dave F

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,
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
 
SUMIF can't "manipulate" an *array* in any of it's agruments.
=SUMIF(D2:D456,--(RIGHT(D2:D456,5)="37057"),Q2:Q456)
Click to expand...

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