SUMPRODUCT: Can you use this and sum the values from multiple colu

[Guest]

Current format used is as follows

=sumproduct(--((Range1=Criteria1)*(Range2=Criteria2)*SumRange))

Is there a way of adding in another range to sum subject to the specifed
criteria?

Thank you in advance

 

[Guest]

=SUMPRODUCT(...) + SUMPRODUCT(...)

make the criteria the same, just use different ranges.

 

[Stephen]

Current format used is as follows

=sumproduct(--((Range1=Criteria1)*(Range2=Criteria2)*SumRange))

Is there a way of adding in another range to sum subject to the specifed
criteria?

Thank you in advance

Just use =SUMPRODUCT(...) + SUMPRODUCT(...)
where both have the same criteria, and the first is based on SumRange and
the second on SumRange2

 

[Bob Phillips]

=sumproduct(--((Range1=Criteria1),--(Range2=Criteria2),SumRange,SumRange2))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

correction, that should be

=SUMPRODUCT(--(Range1=Criteria1),--(Range2=Criteria2),SumRange+SumRange2)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Stephen]

To do it this way you would need brackets around the SumRanges:
=SUMPRODUCT(--(Range1=Criteria1),--(Range2=Criteria2),(SumRange+SumRange2))
Otherwise the criteria would apply only to SumRange and you would add in all
of SumRange2

 

[Bob Phillips]

You reckon?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Stephen]

OK, my apologies. You do need the extra brackets in the slightly different
version
=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2)*(SumRange+SumRange2))
which is what confused me prior to posting.
But I fully accept my mistake and withdraw my faulty "correction".

You reckon?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

 

[rami]

what if the ranges exceed 60 entries, isnt there a way to do this more
efficiently?