Thread: Countif -using with sharepoint gives #value return

working with a simple spreadsheet on sharepoint
The log sheet consisit of
Date,name,booked,completed

This above sheet is created for 5 different shops

I am trying to create a rollup sheet.

I can get count function to work. just not the countif function

=COUNTIF('[Shop1 Tune Up Tracking 2012.xls]Master Log'!$O$2:$O$2500,"Completed")
When I have both workbooks open on my desktop this function works just fine. When I exit and just open the rollup I get #value


This one works fine
=COUNT('[Shop1 Tune Up Tracking 2012.xls]Master Log'!$A$2:$A$2500)

When I exit it will populate the full http: sharepoint link for both of the functions list above.

I am comparing how many leads entered to how many booked(completed)
Thanks

Countif doesn't work with Closed Workbooks, use SUMPRODUCT instead...

=SUMPRODUCT(--('[Shop1 Tune Up Tracking 2012.xls]Master Log'!$O$2:$O$2500="Completed"))

Notice I also have an = sign separating range from criteria instead of a comma like in COUNTIF.....