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

G

Gwynneth

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
 
G

Gary''s Student

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

make the criteria the same, just use different ranges.
 
S

Stephen

Gwynneth said:
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
 
B

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

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

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
 
B

Bob Phillips

You reckon?

--
HTH

Bob

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

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

Bob Phillips said:
You reckon?

--
HTH

Bob

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top