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:Q22 0))
gives a #value! error.

Where have I gone wrong?

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

--
Rick (MVP - Excel)


"Fergus" wrote in message
...
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:Q22 0))
gives a #value! error.

Where have I gone wrong?

--
Fergus

Thanks for the quick response Rick.
--
Fergus


"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Fergus" wrote in message
...
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:Q22 0))
gives a #value! error.

Where have I gone wrong?

--
Fergus