Help with Product Formula "or"

**Slongy** · 07-27-2011, 04:59 AM

Hi

I am using a Sum product formula that checks two cells for validation:

=SUMPRODUCT(--('Complete Pipe'!$N$9:$N$3250=E178),--('Complete Pipe'!$Y$9:$Y$3250=$F$161),'Complete Pipe'!$BH$9:$BH$3250)

I need it to check a 3rd point as well. So I need it to validate that E178 matches, then check whether F161 OR J161 match, then return the results from BH

Can someone help me get the formula right?

I also need to do a 3 point check, where there isn't an "or", so it checks 3 pieces of information and returns the result if all 3 match at the same time

Thanks in advance as always

**Bob Phillips** · 07-27-2011, 05:33 AM

Try

=SUMPRODUCT(--('Complete Pipe'!$N$9:$N$3250=E178),
--(('Complete Pipe'!$Y$9:$Y$3250=$F$161)+('Complete Pipe'!$Y$9:$Y$3250=$J$161)),
'Complete Pipe'!$BH$9:$BH$3250)

**Slongy** · 07-28-2011, 07:35 AM

Perfect Thanks very much

**Slongy** · 07-28-2011, 08:09 AM

Argh this is still not working

I basically need a sum of column FL if 3 matches occur. This is the sum I'm using:

=SUMPRODUCT(--('Complete Pipe'!$P$9:$P$3250=D126),--(('Complete Pipe'!$B$9:$B$3250=$D$30)--('Complete Pipe'!$Y$9:$Y$3250=$E$117)),'Complete Pipe'!$FL$9:$FL$3250)

So it needs to only return a result if the value in Column P matches D126, Column B matches D30 and Column Y matches E117. Then sum all the values in Column FL when the above 3 criteria are met

Any ideas?

Thanks again in advance

**daddylonglegs** · 07-28-2011, 08:14 AM

You don't have the syntax quite right - try this version

=SUMPRODUCT(--('Complete Pipe'!$P$9:$P$3250=D126),--('Complete Pipe'!$B$9:$B$3250=$D$30),--('Complete Pipe'!$Y$9:$Y$3250=$E$117),'Complete Pipe'!$FL$9:$FL$3250)

You could also use SUMIFS in Excel 2007....