Sum Product - AND or OR

  • Thread starter Thread starter Cassie
  • Start date Start date
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
 
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 (e-mail address removed)
 
Correct, careless of me, this non array adaptation
should do it

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