|
Formula should return a result but doesn't
When I bring up the formula box for the following formula (attempting to sum
up numbers in a different sheet given various conditions) -- =SUM(IF(Sheet1!F2:F1900='1340 - 001'!B8,IF(Sheet1!D2:D1900='1340 - 001'!C8,IF(Sheet1!C2:C1900='1340 - 001'!F8,IF(Sheet1!G2:G1900='1340 - 001'!G6,Sheet1!J2:J1900,0),0),0),0)) it shows 'Formula Result'=69, which is correct, that's what it should be. But in the cell itself, it will only show 0. I have this problem with most complex formulas. In a related note, complicated formulas in old spreadsheets work but if I go in there and say, erase a number and then put it back, it has the same problem as above. Help!! |
I think the function that you want is SUMIF(), read up on it. Smock Wrote: When I bring up the formula box for the following formula (attempting to sum up numbers in a different sheet given various conditions) -- =SUM(IF(Sheet1!F2:F1900='1340 - 001'!B8,IF(Sheet1!D2:D1900='1340 - 001'!C8,IF(Sheet1!C2:C1900='1340 - 001'!F8,IF(Sheet1!G2:G1900='1340 - 001'!G6,Sheet1!J2:J1900,0),0),0),0)) it shows 'Formula Result'=69, which is correct, that's what it should be. But in the cell itself, it will only show 0. I have this problem with most complex formulas. In a related note, complicated formulas in old spreadsheets work but if I go in there and say, erase a number and then put it back, it has the same problem as above. Help!! -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=469906 |
With all the conditions that I have set up in this formula, setting it up
this way gives me needed flexibility. This formula would work in an older version of Excel but not in Excel '03. What is it about Excel '03 that is giving me these problems? "Morrigan" wrote: I think the function that you want is SUMIF(), read up on it. Smock Wrote: When I bring up the formula box for the following formula (attempting to sum up numbers in a different sheet given various conditions) -- =SUM(IF(Sheet1!F2:F1900='1340 - 001'!B8,IF(Sheet1!D2:D1900='1340 - 001'!C8,IF(Sheet1!C2:C1900='1340 - 001'!F8,IF(Sheet1!G2:G1900='1340 - 001'!G6,Sheet1!J2:J1900,0),0),0),0)) it shows 'Formula Result'=69, which is correct, that's what it should be. But in the cell itself, it will only show 0. I have this problem with most complex formulas. In a related note, complicated formulas in old spreadsheets work but if I go in there and say, erase a number and then put it back, it has the same problem as above. Help!! -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=469906 |
Is this what you want: =SUMPRODUCT(--(Sheet1!F2:F1900='1340 - 001'!B8),--(Sheet1!D2:D1900='1340 - 001'!C8),--(Sheet1!C2:C1900='1340 - 001'!F8),--(Sheet1!G2:G1900='1340 - 001'!G6),Sheet1!J2:J1900) -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=469906 |
Morrigan, thanks for your help, that's exactly what I wanted. Appreciate it.
Smock "Morrigan" wrote: Is this what you want: =SUMPRODUCT(--(Sheet1!F2:F1900='1340 - 001'!B8),--(Sheet1!D2:D1900='1340 - 001'!C8),--(Sheet1!C2:C1900='1340 - 001'!F8),--(Sheet1!G2:G1900='1340 - 001'!G6),Sheet1!J2:J1900) -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=469906 |
| All times are GMT +1. The time now is 04:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com