Hi!

If your formula works "as is" but fails when you enter 26 in a cell and then
reference that cell, it's because the "26" in your formula (and apparently
your range - Y1:Y5809) is TEXT.

Convert those TEXT numbers to numeric numbers:

Select an empty cell and copy it.
Select the range Y1:Y5809
EditPaste SpecialAdd

Then use this formula: (normally entered, not array entered)

=SUMPRODUCT(--(Sheet1!$Y1:$Y5809=A1),--(Sheet1!$AJ1:$AJ5809=CREDIT!$A4))

A1 = 26 (numeric)

Biff

"vect98" wrote in
message ...

I have the array formula:

=SUM(IF((Sheet1!$Y1:$Y5809="26")*(Sheet1!$AJ1:$AJ5 809=CREDIT!$A4),1,0))

It works fine except I want it to reference the cell that the number 26
is in rather than having to have it in quotes, i tried this, but don't
get the same answers as this way. The reason being is more conditions
may be added in the future which would mean for example the 26 could
change to a 34 etc so i rather not have to have someone manually change
that number in the formula and just have it refence a cell.

Thanks.


--
vect98
------------------------------------------------------------------------
vect98's Profile:
http://www.excelforum.com/member.php...o&userid=26365
View this thread: http://www.excelforum.com/showthread...hreadid=469366