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

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

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

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