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" wrote in message
...
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" wrote in message
...
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