SUMIF CSE formula not working?

[StuLux]

This CSE formula is not giving the expected results and I am tearing my hair out as to why? No matter what values I put in column F to match too Excel is only returning one value not the total e.g. if I have two entries with reference 2010-20 in column F with two values in column AZ (say 13 and 48) the forumal is only returning 48 not the expected 61. Any ideas? I've checked the formatting of the cells, made sure the references are definitely the same etc.

{=SUM(IF(Directory!$F$1:$F$500=$B2,Directory!$AZ$2:$AZ$500,0))}

Doh! Doh! Doh! - how stupid am I - just spotted that the arrays aren't the same dimensions!!!!!!!! Sorry for wasting anybody's time if you've looked at this!

 

[Peter_SSs]

{=SUM(IF(Directory!$F$1:$F$500=$B2,Directory!$AZ$2:$AZ$500,0))}

Try making the ranges the same size.

 

[barry houdini]

That formula, as written will give you an #N/A error because of the mismatched ranges so I presume they are the same size in reality.....

If that's just a typo on here then the error might be caused by some numbers in the sum range being text (and you can't check that by checking the cell formatting). Do you get the correct result with this version?

=SUM(IF(Directory!$F$2:$F$500=$B2,Directory!$AZ$2:$AZ$500+0))

or try converting the sum range to numeric. Select column AZ and use

data > text to columns > finish

Note: in most cases you'd just use SUMIF, i.e.

=SUMIF(Directory!$F$2:$F$500,$B2,Directory!$AZ$2:$AZ$500)

but you'll still have problems if the sum range isn't all numeric