Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUMIF - 2 conditions - with references
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
=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 |
#10
|
|||
|
|||
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 :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif function with remote cell references | Excel Worksheet Functions | |||
SUMIF based on two conditions | Excel Worksheet Functions | |||
SUMIF with two conditions | Excel Discussion (Misc queries) | |||
sumif with two conditions | Excel Worksheet Functions | |||
SUMIF based on 2 conditions | Excel Worksheet Functions |