The 'or' equivalent within a sumproduct

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

Hi,

I have SUMPRODUCT formulas to handle an 'and' condition across multiple
columns.

For example:

=SUMPRODUCT(($A$2:$A$186 <> "")*($AL$2:$AL$186 = 0)*($AK$2:$AK$186 =
0)*($AO$2:$AO$186=1)*($D$2:$D$186="GSA")*($AQ$2:$AQ$186="")*($AR$2:$AR$186=8
))

If all the conditions stated are true for that row, it results in '1' and
the final result are a count of all rows where those conditions are true.

Here is my problem - for the portion of the formula that states
$D$2:$D$186='GSA' , I need to modify it so it actually takes an 'or'
condition of :

if cell D2 has 'GSA' OR 'TSPA' in it, then count it.

I think I can do the formula in a long, roundabout way by doing an
=SUM( the whole SUMPRODUCT above with GSA in it), (the whole SUMPRODUCT
above with TSPA in it))

but is there a more efficent or simpler way to get the same answer? I'm
thinking of somehow using the 'or' condition within the single SUMPRODUCT
formula.

Thanks in advance for any help... it's greatly appreciated...

Pete
 
for OR use + instead of *
or modify this example
=-SUMPRODUCT((ChecksC={"electricity","water","sewer","garbage"})*(ChecksD))
 
...
...
Here is my problem - for the portion of the formula that states
$D$2:$D$186='GSA' , I need to modify it so it actually takes an 'or'
condition of :

if cell D2 has 'GSA' OR 'TSPA' in it, then count it.
Click to expand...

(($D$2:$D$186="GSA")+($D$2:$D$186="TSPA")>0)

so

=SUMPRODUCT(($A$2:$A$186<>"")*($AL$2:$AL$186=0)*($AK$2:$AK$186=0)
*($AO$2:$AO$186=1)*(($D$2:$D$186="GSA")+($D$2:$D$186="TSPA")>0)
*($AQ$2:$AQ$186="")*($AR$2:$AR$186=8))
 
=SUMPRODUCT(($A$2:$A$186 <> "")*($AL$2:$AL$186 = 0)*($AK$2:$AK$186
=0)*($AO$2:$AO$186=1)*(ISNUMBER(MATCH($D$2:$D$186,ListRange,0))*($AQ$2:$AQ$1
86="")*($AR$2:$AR$186=8))

where ListRange is a range of cells housing GSA, TSPA, etc. The ISNUMBER bit
is effectively an OR structure.
 
Back
Top