#VALUE! with SUMIF

[wildturkey]

Ideas please, I am using a SUMIF and referencing a separate workbook and I update links when opening. All cells with the SUMIF formula come back with #VALUE! and all straight references come back with the correct values.

If I edit links and check source I get 'OK' but only opening the sheet allows the formula to work. If I close the workbook and update source I go straight back to the #VALUE!

SUMIF used =SUMIF('[Sales Log 2011 ML.xlsx]Bookings'!$J$7:$J$300,C$2,'[Sales Log 2011 ML.xlsx]Bookings'!$H$7:$H$300)

Thanks

 

[RoryA]

SUMIF will not work with a closed source workbook. You could replace it with a SUMPRODUCT version instead:

=SUMPRODUCT(--('[Sales Log 2011 ML.xlsx]Bookings'!$J$7:$J$300=C$2),'[Sales Log 2011 ML.xlsx]Bookings'!$H$7:$H$300)

 

[Andrew Poulsom]

SUMIF doesn't work with closed workbooks. Try:

=SUMPRODUCT(--('[Sales Log 2011 ML.xlsx]Bookings'!$J$7:$J$300=C$2),'[Sales Log 2011 ML.xlsx]Bookings'!$H$7:$H$300)

 

[wildturkey]

Thanks Both, but get zeros accross the board...?

 

[RoryA]

You may have a mismatch between numbers and numbers stored as text (SUMIF is much more forgiving about that).

 

[wildturkey]

Yep, have played around with the values and when all entered as numbers the formula works fine, my problem is that my range contains

=TEXT(G30,"mmm yy")

as I'm looking to sum all values that fall within a certain month. Back to drawing board.

Thanks again

 

[RoryA]

Which one contains text? (anything of the format "mmm yy" is going to be text, so I don't see how that factors into this)

 

[wildturkey]

I see why you say SUMIF is more forgiving. Have changed my criteria format to text("mmm yy") as well and it works fine.



Thankyou!