SumProduct

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need help figuring out how to look in a range of cells for more than one value in this formula:
=SUMPRODUCT(('PR Tracking'!$A$7:$A$2500="0567")*('PR Tracking'!$J$7:$J$2500="capital")*('PR Tracking'!AO$7:AO$2500))

I need it to look in 'PR Tracking'!$A$7:$A$2500 for 0567 and 0432N. I can not figure out how to make it do this. I have tried everything I know. Any help would be extremely appreciated!!!
 
(1)

=SUMPRODUCT((('PR Tracking'!$A$7:$A$2500="0567")+('PR
Tracking'!$A$7:$A$2500="0432N"))*('PR Tracking'!$J$7:$J$2500="capital")*('PR
Tracking'!AO$7:AO$2500))

(2)

=SUMPRODUCT((('PR Tracking'!$A$7:$A$2500="0567")+('PR
Tracking'!$A$7:$A$2500="0432N"))*('PR Tracking'!$J$7:$J$2500="capital"),'PR
Tracking'!AO$7:AO$2500)

(3a)

=SUMPRODUCT((('PR Tracking'!$A$7:$A$2500="0567")+('PR
Tracking'!$A$7:$A$2500="0432N")),('PR
Tracking'!$J$7:$J$2500="capital")+0,'PR Tracking'!AO$7:AO$2500)

(3b)

=SUMPRODUCT((('PR Tracking'!$A$7:$A$2500="0567")+('PR
Tracking'!$A$7:$A$2500="0432N")),--('PR
Tracking'!$J$7:$J$2500="capital"),'PR Tracking'!AO$7:AO$2500)

The syntax of (3a) and (3b) are kosher, that is, native to SumProduct.

frustratedwthis said:
I need help figuring out how to look in a range of cells for more than one value in this formula:
=SUMPRODUCT(('PR Tracking'!$A$7:$A$2500="0567")*('PR
Click to expand...
Tracking'!$J$7:$J$2500="capital")*('PR Tracking'!AO$7:AO$2500))
I need it to look in 'PR Tracking'!$A$7:$A$2500 for 0567 and 0432N. I can
Click to expand...
not figure out how to make it do this. I have tried everything I know. Any
help would be extremely appreciated!!!
 
This will total c2:c10 if b2:b10 = 1 OR 3
=SUMPRODUCT((B2:B10={1,3})*C2:C10)
To do what you want, AFAIK you will have to have 2 sumproducts and total
them
=sumproduct 1 + sumproduct 2
--
Don Guillett
SalesAid Software
(e-mail address removed)
frustratedwthis said:
I need help figuring out how to look in a range of cells for more than one value in this formula:
=SUMPRODUCT(('PR Tracking'!$A$7:$A$2500="0567")*('PR
Click to expand...
Tracking'!$J$7:$J$2500="capital")*('PR Tracking'!AO$7:AO$2500))
I need it to look in 'PR Tracking'!$A$7:$A$2500 for 0567 and 0432N. I can
Click to expand...
not figure out how to make it do this. I have tried everything I know. Any
help would be extremely appreciated!!!
 
Back
Top