Name Range with three requirements

Excel 2003

This is the formula I have:
=SUMIF((INDEX(QM2S1,,2)),"2-MAKE READY"*INDEX(QM2S1,,3,B2),INDEX(QM2S1,,28))

What I want it to do is - if the second column of range QM2S1 = 2-MAKE
READY, and the third column of range QM2S1 = B2 (date), then sum the 28th
column of range QM2S1.

I've tried both SUMIF (no result), and SUMPRODUCT (# VALUE) result.

TIA,
Carole O

You're multiplying text by something...
try:
=SUMPRODUCT((INDEX(QM2S1,,2)="2-MAKE
READY")*(INDEX(QM2S1,,3)=B2)*INDEX(QM2S1,,28))

"Carole O" <[email protected]> wrote in message
news:[email protected]...
> Excel 2003
>
> This is the formula I have:
> =SUMIF((INDEX(QM2S1,,2)),"2-MAKE
READY"*INDEX(QM2S1,,3,B2),INDEX(QM2S1,,28))
>
> What I want it to do is - if the second column of range QM2S1 = 2-MAKE
> READY, and the third column of range QM2S1 = B2 (date), then sum the 28th
> column of range QM2S1.
>
> I've tried both SUMIF (no result), and SUMPRODUCT (# VALUE) result.
>
> TIA,
> Carole O
>

Bob -

Thanks soooooo much! That did it. I never would have thought of your
solution.

Gratefully,
Carole O


"Bob Umlas" wrote:

> You're multiplying text by something...
> try:
> =SUMPRODUCT((INDEX(QM2S1,,2)="2-MAKE
> READY")*(INDEX(QM2S1,,3)=B2)*INDEX(QM2S1,,28))
>
> "Carole O" <[email protected]> wrote in message
> news:[email protected]...
> > Excel 2003
> >
> > This is the formula I have:
> > =SUMIF((INDEX(QM2S1,,2)),"2-MAKE
> READY"*INDEX(QM2S1,,3,B2),INDEX(QM2S1,,28))
> >
> > What I want it to do is - if the second column of range QM2S1 = 2-MAKE
> > READY, and the third column of range QM2S1 = B2 (date), then sum the 28th
> > column of range QM2S1.
> >
> > I've tried both SUMIF (no result), and SUMPRODUCT (# VALUE) result.
> >
> > TIA,
> > Carole O
> >
>
>
>

try naming the ranges refered to in your equations and use a sumproduct like
=sumproduct(--(range2="2-MAKE READY"),--(range3=$B$2),range 28)
(I have not been able to make a index inside a Sumproduct work)
"Carole O" wrote:

> Excel 2003
>
> This is the formula I have:
> =SUMIF((INDEX(QM2S1,,2)),"2-MAKE READY"*INDEX(QM2S1,,3,B2),INDEX(QM2S1,,28))
>
> What I want it to do is - if the second column of range QM2S1 = 2-MAKE
> READY, and the third column of range QM2S1 = B2 (date), then sum the 28th
> column of range QM2S1.
>
> I've tried both SUMIF (no result), and SUMPRODUCT (# VALUE) result.
>
> TIA,
> Carole O
>