SUMIF returns criteria rather then result?

[Uk Pete]

Can anybody help please,

I'm having trouble with what should be a very simple SUMIF formula.

I have a list in A4:A624 of numbers eg.

59072
59073
59074
etc.

And I have a list of values in L4:L624, some times the list does not contain any values for the criteria searched for (this is when the problem occurs) as they are calucated values but sometimes it does.

The Criteria is in another cell eg. H834 (I have also tried hard coding the criteria as well).

I'm using the following formula =SUMIF($A$4:$A$624,$H$834,L$4:L$624)

The problem is that when the L4:L624 list does not contain any values for the criteria the SUMIF returns the "criteria" number back eg. "59072", if the list in L does contain some values it calculates it correctly.

If the criteria is not in teh list then it returns "0" .

Hope that makes sence.

I have made sure that the criteria cell and the list are the same format but that made no difference?

Any idea's would be usefull

 

[just_jon]

Welcome to MrExcel -- are you perhaps omitting the 3rd argument as in -

=SUMIF($A$4:$A$624,$H$834)

when it returns the criteria?

 

[Uk Pete]

Understand but the third part definitely included.

It seam to be returning the criteria when there is nothing to sum? is that normal?

thanks

uk pete

 

[Aladin Akyurek]

Understand but the third part definitely included.

It seam to be returning the criteria when there is nothing to sum? is that normal?

...

No.

 

[Uk Pete]

Great.......

any idea's on how to fix it though?

does the size of the range effect the operation?

uk pete

 

[just_jon]

Jeez, no idea. The wb emailable?

 

[Uk Pete]

No need I have found the problem.

After the first post reply I investigated somemore,

The problem was in the cells I was summing the calculated value returned a text field "" rather than a 0. My own fault.

I'm still learning..............


Thanks for all the replys

UK Pete

 

[Mr. Chips]

Spelling 2/10