SUMIF returns criteria rather then result?

Uk Pete

New Member
Joined
Nov 29, 2004
Messages
10
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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

New Member
Joined
Nov 29, 2004
Messages
10
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Uk Pete said:
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

New Member
Joined
Nov 29, 2004
Messages
10

ADVERTISEMENT

Great.......

any idea's on how to fix it though?

does the size of the range effect the operation?

uk pete
 

Uk Pete

New Member
Joined
Nov 29, 2004
Messages
10
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,635
Members
414,398
Latest member
dhune

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top