SUMPRODUCT help, please?
 

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.)

SUMPRODUCT help, please?
 

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.)

SUMPRODUCT help, please?
 

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.)

SUMPRODUCT help, please?
 

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.)

SUMPRODUCT help, please?
 

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.)

SUMPRODUCT help, please?
 

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.)

SUMPRODUCT help, please?
 

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

SUMPRODUCT help, please?
 

hi
in addition to Bob to check for all combinations of L5-T including
spaces try using SUBSTITUE
=SUMPRODUCT(((NOT(ISERROR(FIND("L5-T",SUBSTITUE(TIR,"
","")))))*(Dates=20030628)*(Dates<=20040107)*(CLA SS=C4)*(SUBSYSTEM=IND
IRECT(B5)))

Frank

Bob Phillips wrote:
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?


"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

SUMPRODUCT help, please?
 

Its getting late :-(
SUBSTITUTE, not SUBSTITUE
Frank

Frank Kabel wrote:
hi
in addition to Bob to check for all combinations of L5-T including
spaces try using SUBSTITUE
=SUMPRODUCT(((NOT(ISERROR(FIND("L5-T",SUBSTITUE(TIR,"

","")))))*(Dates=20030628)*(Dates<=20040107)*(CLA SS=C4)*(SUBSYSTEM=IND
IRECT(B5)))

Frank

Bob Phillips wrote:
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?


"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

SUMPRODUCT help, please?
 

Frank and Bob:

I have *no idea* why it wouldn't work last night! (except maybe it was late
for me, too - I'll take any excuse!) Set up a bit different - filtered for
my "L5-T*" (after fixing a few errors) and the date range, then pasted these
values to a new area. Figured if I could take out a few variables it might
help. I also realized that, in one instance, I was trying to say "either
this or that" and wound up with "both this and that".

Now I have the following:
=SUMPRODUCT(((FDSC_CLASS=$D$4)*(SUBSYSTEM=$B6)*(CH ARGEABILITY="SHOP1/DNG"))+
((FDSC_CLASS=$D$4)*(SUBSYSTEM=$B6)*(CHARGEABILITY= "SHOP2/DNG")))
where D4 and B6 are cells with text and range CHARGEABILITY has various text
values. And it works!

Thank you both for staying with me. I'll get it someday - I hope!
Ed

"Frank Kabel" wrote in message
...
Its getting late :-(
SUBSTITUTE, not SUBSTITUE
Frank

Frank Kabel wrote:
hi
in addition to Bob to check for all combinations of L5-T including
spaces try using SUBSTITUE
=SUMPRODUCT(((NOT(ISERROR(FIND("L5-T",SUBSTITUE(TIR,"

","")))))*(Dates=20030628)*(Dates<=20040107)*(CLA SS=C4)*(SUBSYSTEM=IND
IRECT(B5)))

Frank

Bob Phillips wrote:
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?


"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

SUMPRODUCT help, please?
 

Hi Ed
good to hear you finally have your solution :-)
Regards
Frank

Ed wrote:
Frank and Bob:

I have *no idea* why it wouldn't work last night! (except maybe it
was late for me, too - I'll take any excuse!) Set up a bit different
- filtered for my "L5-T*" (after fixing a few errors) and the date
range, then pasted these values to a new area. Figured if I could
take out a few variables it might help. I also realized that, in one
instance, I was trying to say "either this or that" and wound up with
"both this and that".

Now I have the following:

=SUMPRODUCT(((FDSC_CLASS=$D$4)*(SUBSYSTEM=$B6)*(CH ARGEABILITY="SHOP1/DN
G"))+
((FDSC_CLASS=$D$4)*(SUBSYSTEM=$B6)*(CHARGEABILITY= "SHOP2/DNG")))
where D4 and B6 are cells with text and range CHARGEABILITY has
various text values. And it works!

Thank you both for staying with me. I'll get it someday - I hope!
Ed

"Frank Kabel" wrote in message
...
Its getting late :-(
SUBSTITUTE, not SUBSTITUE
Frank

Frank Kabel wrote:
hi
in addition to Bob to check for all combinations of L5-T including
spaces try using SUBSTITUE
=SUMPRODUCT(((NOT(ISERROR(FIND("L5-T",SUBSTITUE(TIR,"


","")))))*(Dates=20030628)*(Dates<=20040107)*(CLA SS=C4)*(SUBSYSTEM=IND
IRECT(B5)))

Frank

Bob Phillips wrote:
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?


"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