sumif problems

[Mr Zero]

Hello. I am having problems using the countif function with differen
workbooks. I am trying to create a worksheet that creates a tota
daily uniform inventory (giving a count of the various sizes of eac
uniform item). For example:

=SUM(((COUNTIF('F:\home\Locker\[Uniform Master.xls]Restauran
A'!B2:B41, "xl"))+(COUNTIF('F:\home\Locker\[Unifor
Master.xls]Restaurant B'!B:B, "xl"))))

Here I am counting all size XL garments collected from two differen
worksheets within the source workbook.

I had no problems with this method before (I understand that the sourc
workbook must be open with the countif function). After functionin
properly for serveral days I am now getting a #VALUE! error out of th
blue when I update the spreadsheet with the source open. I haven'
made any changes to the source workbook, either. Can anyone tell m
why this error has appeared?

Also, I create a new worksheet daily so that I can compare my "on hand
to my "needed" . The source workbook is updated as the workforc
changes. Is there a way to only update the newest worksheet leavin
the older worksheets with the numbers from the previous days count??

Thanks for any and all help

 

[Dave Peterson]

Some functions won't work with closed workbooks. =countif() is one of them.

But you can replace each of your =countif()'s with =sumproduct().

=SUMPRODUCT(--('F:\home\Locker\[Uniform Master.xls]Restaurant A'!B2:B41="xl"))

And since you're adding these, you could even drop the outside =sum() stuff.

=sum(3+7)
isn't necessary
=3+7
is fine
or
=sum(3,7)
is ok, too.

Hello. I am having problems using the countif function with different
workbooks. I am trying to create a worksheet that creates a total
daily uniform inventory (giving a count of the various sizes of each
uniform item). For example:

=SUM(((COUNTIF('F:\home\Locker\[Uniform Master.xls]Restaurant
A'!B2:B41, "xl"))+(COUNTIF('F:\home\Locker\[Uniform
Master.xls]Restaurant B'!B:B, "xl"))))

Here I am counting all size XL garments collected from two different
worksheets within the source workbook.

I had no problems with this method before (I understand that the source
workbook must be open with the countif function). After functioning
properly for serveral days I am now getting a #VALUE! error out of the
blue when I update the spreadsheet with the source open. I haven't
made any changes to the source workbook, either. Can anyone tell me
why this error has appeared?

Also, I create a new worksheet daily so that I can compare my "on hand"
to my "needed" . The source workbook is updated as the workforce
changes. Is there a way to only update the newest worksheet leaving
the older worksheets with the numbers from the previous days count??

Thanks for any and all help.

 

[Dave Peterson]

And one more thing, you can't use a whole column with =sumproduct().

So you'll have to shorten up your range (b1:b1000)--just make it big enough to
get all the data you'll ever use--so you don't have to adjust the formula each
time you add rows.

Hello. I am having problems using the countif function with different
workbooks. I am trying to create a worksheet that creates a total
daily uniform inventory (giving a count of the various sizes of each
uniform item). For example:

=SUM(((COUNTIF('F:\home\Locker\[Uniform Master.xls]Restaurant
A'!B2:B41, "xl"))+(COUNTIF('F:\home\Locker\[Uniform
Master.xls]Restaurant B'!B:B, "xl"))))

Here I am counting all size XL garments collected from two different
worksheets within the source workbook.

I had no problems with this method before (I understand that the source
workbook must be open with the countif function). After functioning
properly for serveral days I am now getting a #VALUE! error out of the
blue when I update the spreadsheet with the source open. I haven't
made any changes to the source workbook, either. Can anyone tell me
why this error has appeared?

Also, I create a new worksheet daily so that I can compare my "on hand"
to my "needed" . The source workbook is updated as the workforce
changes. Is there a way to only update the newest worksheet leaving
the older worksheets with the numbers from the previous days count??

Thanks for any and all help.