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

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

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

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