SUMIF not adding cell valuesin range

[Sc0tt1e]

Can anyone help me please, I have spent hours today at work trying to comprehend why my fomula isn't working. I am usingExel 2003 and referencingranges from 2 worksheets.

I would post the spreadsheet but am unable to do so from work ad have no external mail.

=SUMIF(Sheet2!$L$4:$L$54,C6,Sheet1!$L$5:$M$54)

If I change the last range to a sinngle column it picks up the value however if I u the L/M range it only picks up the value in column L, anyone know why or what I can do to stop this happening?

[FDibbins]

I could be wrong on this because I have never seen a sumif() built like that, but Im pretty sure that both ranges need to be on the same sheet

[Sc0tt1e]

I've tried referencing on one sheet but that doesn't produce any values

[daddylonglegs]

SUMIF works on a one-to-one basis so the criteria range needs to be the same size and shape as the sum range (although it doesn't matter if those ranges are on the same sheet or not). If you don't specify the ranges as the same size then Excel implicitly changes the second range for you, so your formula, as written here, is the equivalent of this

=SUMIF(Sheet2!$L$4:$L$54,C6,Sheet1!$L$5:$L$55)

I assume the mismatch in row numbers is a typo but if you want to sum two columns from one criteria column you either need two SUMIFS like this

=SUMIF(Sheet2!$L$4:$L$54,C6,Sheet1!$L$4:$L$54)+SUMIF(Sheet2!$L$4:$L$54,C6,Sheet1!$M$4:$M$54)

or use SUMPRODUCT like this

=SUMPRODUCT((Sheet2!$L$4:$L$54=C6)*Sheet1!$L$4:$M$54)

[FDibbins]

DDL thanks for the update on not needing to be on the same sheet (I didnt notice the range mis-match though)