SUMPRODUCT help, please?

  • Thread starter Thread starter Ed
  • Start date Start date
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.)
 
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)
 
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
 
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)
 
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
 
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
 
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)
 
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
 
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
 
Back
Top