Bob and Frank helped me greatly with a formula based on the SUMPRODUCT
function. I managed to get what they gave me to work fine. Then I messed
with it, and now I need help.

I'm trying to pull my criteria for the ranges from the text values in other
cells. The formula is:
=SUMPRODUCT((NOT(ISERROR(FIND("L5-T",TIR))))*(Dates=20030628)*(Dates<=20040
107)*(CLASS=C4)*(SUBSYSTEM=B5))

where C4 is typed in the cell, and B5=Sheet1!AA1.



Once again, I get a blank - the formula is returning zero. A drop-kick in
the right direction is appreciated!



Ed

(PS - I jumped to a new thread for two reasons: the first one was
apparently resolved early this morning, and the subject of the first one was
"array formula", and I thought getting to SUMPRODUCT would help someone
searching for answers. Hope this didn't screw other things up.)

ED,

This is a punt, but judging from your aside that B5=Sheet1!AA1, try this


=SUMPRODUCT((NOT(ISERROR(FIND("L5-T",TIR))))*(Dates=20030628)*(Dates<=20040
107)*(CLASS=C4)*(SUBSYSTEM=INDIRECT(B5)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ed" wrote in message
...
Bob and Frank helped me greatly with a formula based on the SUMPRODUCT
function. I managed to get what they gave me to work fine. Then I messed
with it, and now I need help.

I'm trying to pull my criteria for the ranges from the text values in
other
cells. The formula is:

=SUMPRODUCT((NOT(ISERROR(FIND("L5-T",TIR))))*(Dates=20030628)*(Dates<=20040
107)*(CLASS=C4)*(SUBSYSTEM=B5))

where C4 is typed in the cell, and B5=Sheet1!AA1.



Once again, I get a blank - the formula is returning zero. A drop-kick in
the right direction is appreciated!



Ed

(PS - I jumped to a new thread for two reasons: the first one was
apparently resolved early this morning, and the subject of the first one
was
"array formula", and I thought getting to SUMPRODUCT would help someone
searching for answers. Hope this didn't screw other things up.)

Adding INDIRECT gave me #REF. Deleting the whole reference still gave me a
blank. The table is on Sheet2 of the same workbook.

Have I missed a certain way to tell the formula to get the text from a cell
and use that?

Ed

"Bob Phillips" wrote in message
...
ED,

This is a punt, but judging from your aside that B5=Sheet1!AA1, try this



=SUMPRODUCT((NOT(ISERROR(FIND("L5-T",TIR))))*(Dates=20030628)*(Dates<=20040
107)*(CLASS=C4)*(SUBSYSTEM=INDIRECT(B5)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ed" wrote in message
...
Bob and Frank helped me greatly with a formula based on the SUMPRODUCT
function. I managed to get what they gave me to work fine. Then I
messed
with it, and now I need help.

I'm trying to pull my criteria for the ranges from the text values in
other
cells. The formula is:


=SUMPRODUCT((NOT(ISERROR(FIND("L5-T",TIR))))*(Dates=20030628)*(Dates<=20040
107)*(CLASS=C4)*(SUBSYSTEM=B5))

where C4 is typed in the cell, and B5=Sheet1!AA1.



Once again, I get a blank - the formula is returning zero. A drop-kick
in
the right direction is appreciated!



Ed

(PS - I jumped to a new thread for two reasons: the first one was
apparently resolved early this morning, and the subject of the first one
was
"array formula", and I thought getting to SUMPRODUCT would help someone
searching for answers. Hope this didn't screw other things up.)

Ed,

What is in CLASS (couple of examples), SUSBSYTEM (ditto), and C4?

And what exactly do you mean by '... Have I missed a certain way to tell the
formula to get the text from a cell and use that?...'

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ed" wrote in message
...
Adding INDIRECT gave me #REF. Deleting the whole reference still gave me
a
blank. The table is on Sheet2 of the same workbook.

Have I missed a certain way to tell the formula to get the text from a
cell
and use that?

Ed

"Bob Phillips" wrote in message
...
ED,

This is a punt, but judging from your aside that B5=Sheet1!AA1, try this




=SUMPRODUCT((NOT(ISERROR(FIND("L5-T",TIR))))*(Dates=20030628)*(Dates<=20040
107)*(CLASS=C4)*(SUBSYSTEM=INDIRECT(B5)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ed" wrote in message
...
Bob and Frank helped me greatly with a formula based on the SUMPRODUCT
function. I managed to get what they gave me to work fine. Then I
messed
with it, and now I need help.

I'm trying to pull my criteria for the ranges from the text values in
other
cells. The formula is:



=SUMPRODUCT((NOT(ISERROR(FIND("L5-T",TIR))))*(Dates=20030628)*(Dates<=20040
107)*(CLASS=C4)*(SUBSYSTEM=B5))

where C4 is typed in the cell, and B5=Sheet1!AA1.



Once again, I get a blank - the formula is returning zero. A
drop-kick
in
the right direction is appreciated!



Ed

(PS - I jumped to a new thread for two reasons: the first one was
apparently resolved early this morning, and the subject of the first
one
was
"array formula", and I thought getting to SUMPRODUCT would help
someone
searching for answers. Hope this didn't screw other things up.)

I feel stupid, Bob! I was playing with it and took it down to just the two
references to cell values:
=SUMPRODUCT((CLASS=$H$4)*(SUBSYSTEM=$B5))
This works fine! I guess what that means is that when I'm calling another
criteria, it's not exactly the same in every cell (ie: "L5-T" might also be
"L 5-T"). Now I've got to find a way to scan every entry and validate it -
or maybe give up and do this manually (filter, count rows, repeat).

I'll keep playing and see what happens. Thank you for your continued
interest and help.
Ed

"Bob Phillips" wrote in message
...
Ed,

What is in CLASS (couple of examples), SUSBSYTEM (ditto), and C4?

And what exactly do you mean by '... Have I missed a certain way to tell
the
formula to get the text from a cell and use that?...'

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ed" wrote in message
...
Adding INDIRECT gave me #REF. Deleting the whole reference still gave
me
a
blank. The table is on Sheet2 of the same workbook.

Have I missed a certain way to tell the formula to get the text from a
cell
and use that?

Ed

"Bob Phillips" wrote in message
...
ED,

This is a punt, but judging from your aside that B5=Sheet1!AA1, try
this





=SUMPRODUCT((NOT(ISERROR(FIND("L5-T",TIR))))*(Dates=20030628)*(Dates<=20040
107)*(CLASS=C4)*(SUBSYSTEM=INDIRECT(B5)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ed" wrote in message
...
Bob and Frank helped me greatly with a formula based on the
SUMPRODUCT
function. I managed to get what they gave me to work fine. Then I
messed
with it, and now I need help.

I'm trying to pull my criteria for the ranges from the text values
in
other
cells. The formula is:




=SUMPRODUCT((NOT(ISERROR(FIND("L5-T",TIR))))*(Dates=20030628)*(Dates<=20040
107)*(CLASS=C4)*(SUBSYSTEM=B5))

where C4 is typed in the cell, and B5=Sheet1!AA1.



Once again, I get a blank - the formula is returning zero. A
drop-kick
in
the right direction is appreciated!



Ed

(PS - I jumped to a new thread for two reasons: the first one was
apparently resolved early this morning, and the subject of the first
one
was
"array formula", and I thought getting to SUMPRODUCT would help
someone
searching for answers. Hope this didn't screw other things up.)

ED,

If it is the L5-T, you could always use OR, like this

=SUMPRODUCT(((NOT(ISERROR(FIND("L5-T",TIR))))+(NOT(ISERROR(FIND("L
5-T",TIR))))*(Dates=20030628)*(Dates<=20040107)*(CL ASS=C4)*(SUBSYSTEM=INDIR
ECT(B5)))

I haven't tested this, and it gets more complex if you could have L 5-T,
L5-T, L5 -T, etc., but give it a try

And anyway, have you learnt anything, because that is important?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ed" wrote in message
...
I feel stupid, Bob! I was playing with it and took it down to just the
two
references to cell values:
=SUMPRODUCT((CLASS=$H$4)*(SUBSYSTEM=$B5))
This works fine! I guess what that means is that when I'm calling another
criteria, it's not exactly the same in every cell (ie: "L5-T" might also
be
"L 5-T"). Now I've got to find a way to scan every entry and validate
it -
or maybe give up and do this manually (filter, count rows, repeat).

I'll keep playing and see what happens. Thank you for your continued
interest and help.
Ed

Hi Ed
in addition to Bob you may use the following procedure to determine
which part of your SUMPRODUCT formula goes wrong:
- start with only one condition:
=SUMPRODUCT(--(NOT(ISERROR(FIND("L5-T",TIR)))))
(the -- is required for only one condition to coerce the boolean values
to numbers)
- Add part after part:
=SUMPRODUCT((NOT(ISERROR(FIND("L5-T",TIR))))*(Dates=20030628))
.....


This will help you to identify which part goes wrong. With that result
come back to us :-)
Frank




Ed wrote:
Adding INDIRECT gave me #REF. Deleting the whole reference still
gave me a blank. The table is on Sheet2 of the same workbook.

Have I missed a certain way to tell the formula to get the text from
a cell and use that?

Ed

"Bob Phillips" wrote in message
...
ED,

This is a punt, but judging from your aside that B5=Sheet1!AA1, try
this




=SUMPRODUCT((NOT(ISERROR(FIND("L5-T",TIR))))*(Dates=20030628)*(Dates<=
20040
107)*(CLASS=C4)*(SUBSYSTEM=INDIRECT(B5)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ed" wrote in message
...
Bob and Frank helped me greatly with a formula based on the
SUMPRODUCT function. I managed to get what they gave me to work
fine. Then I messed with it, and now I need help.

I'm trying to pull my criteria for the ranges from the text values
in other cells. The formula is:



=SUMPRODUCT((NOT(ISERROR(FIND("L5-T",TIR))))*(Dates=20030628)*(Dates<=
20040
107)*(CLASS=C4)*(SUBSYSTEM=B5))

where C4 is typed in the cell, and B5=Sheet1!AA1.



Once again, I get a blank - the formula is returning zero. A
drop-kick in the right direction is appreciated!



Ed

(PS - I jumped to a new thread for two reasons: the first one was
apparently resolved early this morning, and the subject of the
first one was "array formula", and I thought getting to SUMPRODUCT
would help someone searching for answers. Hope this didn't screw
other things up.)