SUMPRODUCT help, please?

E

Ed

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

Bob Phillips

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

Ed

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
 
B

Bob Phillips

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

Frank Kabel

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
 
E

Ed

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
 
B

Bob Phillips

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)*(CLASS=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)
 
F

Frank Kabel

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)*(CLASS=C4)*(SUBSYSTEM=IND
IRECT(B5)))

Frank
 
E

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)*(CHARGEABILITY="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
 
Top