Sum Product - AND or OR

C

Cassie

If I use the following formula for comparing using AND
What do I use for OR

=SUMPRODUCT(($P$2:$P$41694='Fixed Data'!$E$5)*
($Q$2:$Q$41694='Fixed Data'!$F$5),$J$2:$J$41694)

Thanks
Very Much
Cassie
 
C

Chip Pearson

That won't work with SUMPRODUCT, because some entries will be
multiplied by 2. Use

=SUM(IF((A1:A10=1)+(B1:B10=1),C1:C10,FALSE))

entered as array formula instead. Change the cell references
accordingly. Since this is an array formula, you must press
Ctrl+Shift+Enter rather than just Enter when you first enter the
formula and whenever you edit it later. If you do this properly,
Excel will display the formula enclosed in curly braces {}.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com [email protected]
 
P

Peo Sjoblom

Correct, careless of me, this non array adaptation
should do it

=SUMPRODUCT(--((A1:A10=1)+(B1:B10=1)>0),C1:C10)
 
Top