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

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

hey thanks for your help, i converted both columns to numbers but it
still doesnt work , just comes up with 0 which it shouldnt. the 26 is
one condition the other condition is a date, so maybe that is affecting
it??


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

Hi!

Try this:

Real dates are just numbers formatted to look like dates.

9/20/2005 is really the numeric value 38615 formatted to look like 9/20/2005

Make sure CREDIT!$A4 is a real date.

=ISNUMBER(CREDIT!$A4) should return TRUE

Try to find corresponding cells in your range that match each criteria:

Y200 = 26
AJ200 = CREDIT!$A4

=ISNUMBER(Y200) should return TRUE
=ISNUMBER(AJ200) should return TRUE

Assume AJ200 "matches" the date entered in CREDIT!$A4

=AJ200=CREDIT!$A4 should return TRUE

Biff

"vect98" wrote in
message ...

hey thanks for your help, i converted both columns to numbers but it
still doesnt work , just comes up with 0 which it shouldnt. the 26 is
one condition the other condition is a date, so maybe that is affecting
it??


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

Hi i tried that =ISNUMBER(AJ200) thing and it worked ok for the AJ
column, but when I did it for the Y column it returns false even though
i made sure the column was in Number format (no decimals) so i'm not
sure why its returnging false, thus im thinking this is why ur original
formula isnt working.

Thanks : )


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

Hi!

OK, you can't convert TEXT numbers to NUMERIC numbers by changing the
format. Did you try this:

Copy an empty cell
Select the range Y1:Y5809
EditPaste SpecialAdd

Biff

"vect98" wrote in
message ...

Hi i tried that =ISNUMBER(AJ200) thing and it worked ok for the AJ
column, but when I did it for the Y column it returns false even though
i made sure the column was in Number format (no decimals) so i'm not
sure why its returnging false, thus im thinking this is why ur original
formula isnt working.

Thanks : )


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

that seems to clear the actual contents of those cells (i did the paste
special - add) hmm. sorry im being such a pain.


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

its ok worked sorry.


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

=SUMPRODUCT(--(Sheet1!$X1:$X5809="SUCCESS"),--(Sheet1!$AJ1:$AJ5809=SUCCESSFULRes!$A2))


I want to add another condition to this so its for success and failure.
Ive tried copying the first part and adding it and also tried putting an
AND condition in but it still wont worked.

TIA :)


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

If you mean count the number of times Column X contains 'Success' or
'Failure', try...

=SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!$X1:$X5809,{"Success","Fail ure"},0))
),--(Sheet1!$AJ1:$AJ5809=SUCCESSFULRes!$A2))

Hope this helps!

In article ,
vect98 wrote:

=SUMPRODUCT(--(Sheet1!$X1:$X5809="SUCCESS"),--(Sheet1!$AJ1:$AJ5809=SUCCESSFULR
es!$A2))


I want to add another condition to this so its for success and failure.
Ive tried copying the first part and adding it and also tried putting an
AND condition in but it still wont worked.

TIA :)