Multiple SUMPRODUCT

[catlair]

Hi,

Wondering if there is an easier way or a "summarize" version to use the
SUMPRODUCT for multiple values. For example,

(SUMPRODUCT(--($A$2:$A$170="AP"),--($D$2:$D$170>=7),--($G$2:$G$170="Reactive"),--($K$2:$K$170="ID")))
+(SUMPRODUCT(--($A$2:$A$170="AP-HOME"),--($D$2:$D$170>=7),--($G$2:$G$170="Reactive"),--($K$2:$K$170="ID")))
+(SUMPRODUCT(--($A$2:$A$170="AP-INT"),--($D$2:$D$170>=7),--($G$2:$G$170="Reactive"),--($K$2:$K$170="ID")))

I can't seem to make the "AP", "AP-HOME" & "AP-INT" values work in one
SUMPRODUCT equation.

 

[JE McGimpsey]

Wondering if there is an easier way or a "summarize" version to use the
SUMPRODUCT for multiple values. For example,

(SUMPRODUCT(--($A$2:$A$170="AP"),--($D$2:$D$170>=7),--($G$2:$G$170="Reactive")
,--($K$2:$K$170="ID")))
+(SUMPRODUCT(--($A$2:$A$170="AP-HOME"),--($D$2:$D$170>=7),--($G$2:$G$170="Reac
tive"),--($K$2:$K$170="ID")))
+(SUMPRODUCT(--($A$2:$A$170="AP-INT"),--($D$2:$D$170>=7),--($G$2:$G$170="React
ive"),--($K$2:$K$170="ID")))

One way:

=SUMPRODUCT((($A$2:$A$170="AP") + ($A$2:$A$170="AP-HOME") +
($A$2:$A$170="AP-INT")), --($D$2:$D$170>=7), --($G$2:$G$170="Reactive"),
--($K$2:$K$170="ID"))

Note that, since the first terms are added, no double negative is needed.


Alternatively, if any string starting with AP should be summarized:

=SUMPRODUCT(--(LEFT($A$2:$A$170,2)="AP") --($D$2:$D$170>=7),
--($G$2:$G$170="Reactive"), --($K$2:$K$170="ID"))

 

[catlair]

Cool! Thanks!

One way:

=SUMPRODUCT((($A$2:$A$170="AP") + ($A$2:$A$170="AP-HOME") +
($A$2:$A$170="AP-INT")), --($D$2:$D$170>=7), --($G$2:$G$170="Reactive"),
--($K$2:$K$170="ID"))

Note that, since the first terms are added, no double negative is needed.


Alternatively, if any string starting with AP should be summarized:

=SUMPRODUCT(--(LEFT($A$2:$A$170,2)="AP") --($D$2:$D$170>=7),
--($G$2:$G$170="Reactive"), --($K$2:$K$170="ID"))