View Single Post
  #13   Report Post  
Posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
Posts: 109
Default Sum product formula with conditions

On 7/17/2019 11:34 AM, dpb wrote:
On 7/17/2019 9:46 AM, TIMOTHY wrote:
Thank you Alan & Rover

I have searched on google and found out the formula

=sumproduct(--(Range1,criteria1),--(Range2,criteria2),Range3,Range4)


=sumproduct(num(Range1,criteria1),num(Range2,crite ria2),Range3,Range4)

I don't know who started this idiom of a double-negation operator
instead...but it's a least confusing to read if nothing else and seems
less efficient besides.


ERRATUM: The cast-to-numeric function is N(), not NUM(), sorry...

=sumproduct(n(Range1,criteria1),n(Range2,criteria2 ),Range3,Range4)

--