Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sumif range returns #NUM!
Windows 2000Pro, MSO 2000 Pro
I have two spreadsheets on which I create "Unique" record identifiers by concatenating two cells using "=A2 & I2" format. I then have the formula "=SUMIF('102804'!$H:$H,F3,'102804'!$I:$I)" where the F3 and $H:$H references are to these two columns. The Sumif is returning completely erroneous data and when I try to re-create the formula using the wizard, the wizard returns #NUM! as soon as the H:H range formula is added.... The range Column $H:$H and "F" are both formatted text, and I have tried other concat options and the trim function to try to "marry up" the values.... Any suggestions/explanations would be most appreciated Thanx |
#2
|
|||
|
|||
Hi
do you have error in these columns? -- Regards Frank Kabel Frankfurt, Germany BAC wrote: Windows 2000Pro, MSO 2000 Pro I have two spreadsheets on which I create "Unique" record identifiers by concatenating two cells using "=A2 & I2" format. I then have the formula "=SUMIF('102804'!$H:$H,F3,'102804'!$I:$I)" where the F3 and $H:$H references are to these two columns. The Sumif is returning completely erroneous data and when I try to re-create the formula using the wizard, the wizard returns #NUM! as soon as the H:H range formula is added.... The range Column $H:$H and "F" are both formatted text, and I have tried other concat options and the trim function to try to "marry up" the values.... Any suggestions/explanations would be most appreciated Thanx |
#3
|
|||
|
|||
No.. The column with the SumIf Function is returning values where there
shouldn't be any (I Know the "criteria" does not exist), or may double the amount that should be there, or is returning zero where I know a value should exist. As best I can tell, the function is not returning any error results (such as #N/A, etc) Thanx "Frank Kabel" wrote: Hi do you have error in these columns? -- Regards Frank Kabel Frankfurt, Germany BAC wrote: Windows 2000Pro, MSO 2000 Pro I have two spreadsheets on which I create "Unique" record identifiers by concatenating two cells using "=A2 & I2" format. I then have the formula "=SUMIF('102804'!$H:$H,F3,'102804'!$I:$I)" where the F3 and $H:$H references are to these two columns. The Sumif is returning completely erroneous data and when I try to re-create the formula using the wizard, the wizard returns #NUM! as soon as the H:H range formula is added.... The range Column $H:$H and "F" are both formatted text, and I have tried other concat options and the trim function to try to "marry up" the values.... Any suggestions/explanations would be most appreciated Thanx |
#4
|
|||
|
|||
Hi
if you like email me your sample file -- Regards Frank Kabel Frankfurt, Germany BAC wrote: No.. The column with the SumIf Function is returning values where there shouldn't be any (I Know the "criteria" does not exist), or may double the amount that should be there, or is returning zero where I know a value should exist. As best I can tell, the function is not returning any error results (such as #N/A, etc) Thanx "Frank Kabel" wrote: Hi do you have error in these columns? -- Regards Frank Kabel Frankfurt, Germany BAC wrote: Windows 2000Pro, MSO 2000 Pro I have two spreadsheets on which I create "Unique" record identifiers by concatenating two cells using "=A2 & I2" format. I then have the formula "=SUMIF('102804'!$H:$H,F3,'102804'!$I:$I)" where the F3 and $H:$H references are to these two columns. The Sumif is returning completely erroneous data and when I try to re-create the formula using the wizard, the wizard returns #NUM! as soon as the H:H range formula is added.... The range Column $H:$H and "F" are both formatted text, and I have tried other concat options and the trim function to try to "marry up" the values.... Any suggestions/explanations would be most appreciated Thanx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF with only one criteria cell within range | Excel Discussion (Misc queries) | |||
can I use a range of dates as a criteria when using sumif? | Excel Worksheet Functions | |||
SUMIF multiple criteria in 1 range | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions | |||
SUMIF across a range of worksheets | Excel Worksheet Functions |