sumif is referencing blank cells and providing a value

[Gooford]

Hi,

How do I get 'SUMIF' to ignore blank cells?

sumif(a1:a10,h1,b1:b10) - then fill down

a= ID number
h1 = ID number
b= cost info

eg 'matt' in 'h' is finding 'matt' in 'a' and reporting on the cost info against that name in 'b', but in the middle of the list a blank is matching a blank and returning a subtotal which is in the cost column.

I want to put sumif(a1:a10<>"",h1, b1:b10) but this doesnt work!

Thanks and regards

[DonkeyOte]

Can you post a sample - I for one am struggling to visualise what you're doing... ie your sample formula has no absolute references...

A SUMIF will only return a value where criteria range is blank if the criteria itself is blank... if that's the case and you want to avoid this then perhaps:

=IF($H1="","",SUMIF($A$1:$A$10,$H1,$B$1:$B$10))
copied down

[Gooford]

You have hit the nail on the head. The cell in h was blank in the one instance. Your formula has sorted it out, thank you very much!