SUMIF - 2 conditions - with references

I have the array formula:

=SUM(IF((Sheet1!$Y1:$Y5809="26")*(Sheet1!$AJ1:$AJ5809=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.

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
Edit>Paste Special>Add

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

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

A1 = 26 (numeric)

Biff

"vect98" <[email protected]> wrote in
message news:[email protected]...
>
> I have the array formula:
>
> =SUM(IF((Sheet1!$Y1:$Y5809="26")*(Sheet1!$AJ1:$AJ5809=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??

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 : )

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
Edit>Paste Special>Add

Biff

"vect98" <[email protected]> wrote in
message news:[email protected]...
>
> 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.

its ok worked sorry.

=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

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

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

Hope this helps!

In article <[email protected]>,
vect98 <[email protected]> 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