SUMPRODUCT problems

[Fergus]

I am trying to use SUMPRODUCT for summing instances where cells contain part
of the word “Ultra†for the period 1/06/2009 to 30/06/2009 (dd,mm,yyyy
format).

=SUMPRODUCT(--(I63:I222>=--"1-06-2009"),--(I63:I222<=--"30-06-2009")) works
ok
=SUMPRODUCT(ISNUMBER(SEARCH("Ultra",F63:F220))*Q63:Q220)
Works ok

But when combined
=SUMPRODUCT(--(I63:I222>=--"1-06-2009"),--(I63:I222<=--"30-06-2009"),(ISNUMBER(SEARCH("Ultra",F63:F220))*Q63:Q220))
gives a #value! error.

Where have I gone wrong?

 

[Rick Rothstein]

You have two different size ranges (x63:x222 and x63:x220 where x is just a
column letter stand-in) in the combined formula... in a SUMPRODUCT (as with
all array formulas) all ranges being iterated over must span the same number
of cells (so either change the 222 to 220 or the 220 to 222 whichever is
appropriate).

 

[Fergus]

Thanks for the quick response Rick.
--
Fergus

You have two different size ranges (x63:x222 and x63:x220 where x is just a
column letter stand-in) in the combined formula... in a SUMPRODUCT (as with
all array formulas) all ranges being iterated over must span the same number
of cells (so either change the 222 to 220 or the 220 to 222 whichever is
appropriate).