Countif links not working

I have multiple workbooks with multiple sheets (don't ask...)
Anyway, I have a paycheck calculator that does all the math for OT/DT etc.
that works great.
However, if the tech is on call, he marks a X in the On Call box which in
turn makes another workbook cell say Yes.
In my third paycheck workbook I'm using a Countif "yes" to total the number
of days the tech was on call and then multiply that by the on call pay.
This is the only link that doesn't work and I recall reading that countif
doesn't work if the source isn't open (which is generally the case).
Any suggestions? A macro? A different tactic?
The dates are in one column, and the "Yes" are in another column. Possibly
returning a number instead of yes?
Help! Sick and brain quit working!

you can use sumproduct((range=whatever)*(range)) where range is the
range you would have put in the sumif formual and whatever is the
criteria


--
duane
------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=265947

Hmm, doesn't seem to work for me. Does "whatever" need to be numeric?

"duane" wrote:


you can use sumproduct((range=whatever)*(range)) where range is the
range you would have put in the sumif formual and whatever is the
criteria


--
duane
------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=265947

should work for range="abc", with abc the criteria


--
duane
------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=265947

or in your case. range="yes"


--
duane
------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=265947

Hi
post your current COUNTIF formula. It is then very simple to convert it
to a working SUMPRODUCT formula. e.g.

=COUNTIF('c:\temp\[testbook.xls].sheet'!A1:A100,"condition")

should be converted to:
=SUMPRODUCT(--('c:\temp\[testbook.xls].sheet'!A1:A100="condition"))


--
Regards
Frank Kabel
Frankfurt, Germany


pkley wrote:
I have multiple workbooks with multiple sheets (don't ask...)
Anyway, I have a paycheck calculator that does all the math for OT/DT
etc. that works great.
However, if the tech is on call, he marks a X in the On Call box
which in turn makes another workbook cell say Yes.
In my third paycheck workbook I'm using a Countif "yes" to total the
number of days the tech was on call and then multiply that by the on
call pay. This is the only link that doesn't work and I recall
reading that countif doesn't work if the source isn't open (which is
generally the case).
Any suggestions? A macro? A different tactic?
The dates are in one column, and the "Yes" are in another column.
Possibly returning a number instead of yes?
Help! Sick and brain quit working!

Ok, here's the current formula, but I'm pretty sure I've uncovered a larger
issue, which may involve a macro I think...
=COUNTIF('C:\Documents and Settings\Peter\My
Documents\PRK\[Payroll_Master_Redding_2004.XLS]OCT 04'!$B$8:$B$22,"YES")
I've tried changing it to what you suggested, =sumproduct(--('C:\Documents
and Settings\Peter\My Documents\PRK\[Payroll_Master_Redding_2004.XLS]OCT
04'!$B$8:$B$22,"YES")) - it didn't seem to like either the -- or the extra
set of brackets... it's running on Excel 2000...

But before you expend wasted effort, the larger issue is the automatic
update of links. Here's the current process...
Techs enter their times on the Timesheet workbook which is colocated in a
folder with their Payroll Master workbook. They are SUPPOSED to open their
Payroll Master to verify that everything is right, but they generally don't -
which means that the values in the Payroll Master don't update.
Each tech has their own folder. In a separate folder I have the Payroll
Entry/Paycheck Calculator workbook that Managers and Accounting have access
to to enter Additional time, PTO, ect. which populates cells on each tech's
Payroll Master. It also uses the Payroll Master as a source so that they have
easy viewing of pertinent information without opening each tech's Payroll
Master. One final note, Each Payroll Master is named the same (which I can
change if necessary - I know you can't have multiple files open with the same
name).
The issue here is that the techs will enter their info but not update the
links on the Payroll Master. When Accounting and Managers go to enter their
data, the reference data is not updated because the Payroll Master hasn't
updated.
I hope this hasn't totally lost you, and yes I'm sure there was a better way
of constructing this whole thing, but I started out not knowing anything
about Excel a year ago...
I'm guessing there's a macro that can autoupdated all related links thru
multiple workbooks?
On another note, for some odd reason the links in Payroll Master keep losing
connection to Payroll Entry. Every time I open Payroll Master and update
links (on opening) it says it can't find "Payroll Entry" and I have to
manually tell it where to go. Any suggestions?
On final note, I'll send the workbooks involved if you want, just give me an
address to send to...

"Frank Kabel" wrote:

Hi
post your current COUNTIF formula. It is then very simple to convert it
to a working SUMPRODUCT formula. e.g.

=COUNTIF('c:\temp\[testbook.xls].sheet'!A1:A100,"condition")

should be converted to:
=SUMPRODUCT(--('c:\temp\[testbook.xls].sheet'!A1:A100="condition"))


--
Regards
Frank Kabel
Frankfurt, Germany


pkley wrote:
I have multiple workbooks with multiple sheets (don't ask...)
Anyway, I have a paycheck calculator that does all the math for OT/DT
etc. that works great.
However, if the tech is on call, he marks a X in the On Call box
which in turn makes another workbook cell say Yes.
In my third paycheck workbook I'm using a Countif "yes" to total the
number of days the tech was on call and then multiply that by the on
call pay. This is the only link that doesn't work and I recall
reading that countif doesn't work if the source isn't open (which is
generally the case).
Any suggestions? A macro? A different tactic?
The dates are in one column, and the "Yes" are in another column.
Possibly returning a number instead of yes?
Help! Sick and brain quit working!

Hi
for your formula use:
=sumproduct(--('C:\Documents
and Settings\Peter\My
Documents\PRK\[Payroll_Master_Redding_2004.XLS]OCT
04'!$B$8:$B$22="YES"))

--
Regards
Frank Kabel
Frankfurt, Germany


pkley wrote:
Ok, here's the current formula, but I'm pretty sure I've uncovered a
larger issue, which may involve a macro I think...
=COUNTIF('C:\Documents and Settings\Peter\My
Documents\PRK\[Payroll_Master_Redding_2004.XLS]OCT
04'!$B$8:$B$22,"YES") I've tried changing it to what you suggested,
=sumproduct(--('C:\Documents and Settings\Peter\My
Documents\PRK\[Payroll_Master_Redding_2004.XLS]OCT
04'!$B$8:$B$22,"YES")) - it didn't seem to like either the -- or the
extra set of brackets... it's running on Excel 2000...

But before you expend wasted effort, the larger issue is the
automatic
update of links. Here's the current process...
Techs enter their times on the Timesheet workbook which is colocated
in a folder with their Payroll Master workbook. They are SUPPOSED to
open their Payroll Master to verify that everything is right, but
they generally don't - which means that the values in the Payroll
Master don't update.
Each tech has their own folder. In a separate folder I have the
Payroll Entry/Paycheck Calculator workbook that Managers and
Accounting have access to to enter Additional time, PTO, ect. which
populates cells on each tech's Payroll Master. It also uses the
Payroll Master as a source so that they have easy viewing of
pertinent information without opening each tech's Payroll Master. One
final note, Each Payroll Master is named the same (which I can change
if necessary - I know you can't have multiple files open with the
same name).
The issue here is that the techs will enter their info but not update
the links on the Payroll Master. When Accounting and Managers go to
enter their data, the reference data is not updated because the
Payroll Master hasn't updated.
I hope this hasn't totally lost you, and yes I'm sure there was a
better way of constructing this whole thing, but I started out not
knowing anything about Excel a year ago...
I'm guessing there's a macro that can autoupdated all related links
thru multiple workbooks?
On another note, for some odd reason the links in Payroll Master keep
losing connection to Payroll Entry. Every time I open Payroll Master
and update links (on opening) it says it can't find "Payroll Entry"
and I have to manually tell it where to go. Any suggestions?
On final note, I'll send the workbooks involved if you want, just
give me an address to send to...

"Frank Kabel" wrote:

Hi
post your current COUNTIF formula. It is then very simple to convert
it to a working SUMPRODUCT formula. e.g.

=COUNTIF('c:\temp\[testbook.xls].sheet'!A1:A100,"condition")

should be converted to:
=SUMPRODUCT(--('c:\temp\[testbook.xls].sheet'!A1:A100="condition"))


--
Regards
Frank Kabel
Frankfurt, Germany


pkley wrote:
I have multiple workbooks with multiple sheets (don't ask...)
Anyway, I have a paycheck calculator that does all the math for
OT/DT etc. that works great.
However, if the tech is on call, he marks a X in the On Call box
which in turn makes another workbook cell say Yes.
In my third paycheck workbook I'm using a Countif "yes" to total
the
number of days the tech was on call and then multiply that by the
on
call pay. This is the only link that doesn't work and I recall
reading that countif doesn't work if the source isn't open (which
is
generally the case).
Any suggestions? A macro? A different tactic?
The dates are in one column, and the "Yes" are in another column.
Possibly returning a number instead of yes?
Help! Sick and brain quit working!

Sorry, already tried that. The description in help (excel 2000) states that
sumproduct treats non numeric values as 0. Don't think sumproduct is the
answer...

"Frank Kabel" wrote:

Hi
for your formula use:
=sumproduct(--('C:\Documents
and Settings\Peter\My
Documents\PRK\[Payroll_Master_Redding_2004.XLS]OCT
04'!$B$8:$B$22="YES"))

--
Regards
Frank Kabel
Frankfurt, Germany


pkley wrote:
Ok, here's the current formula, but I'm pretty sure I've uncovered a
larger issue, which may involve a macro I think...
=COUNTIF('C:\Documents and Settings\Peter\My
Documents\PRK\[Payroll_Master_Redding_2004.XLS]OCT
04'!$B$8:$B$22,"YES") I've tried changing it to what you suggested,
=sumproduct(--('C:\Documents and Settings\Peter\My
Documents\PRK\[Payroll_Master_Redding_2004.XLS]OCT
04'!$B$8:$B$22,"YES")) - it didn't seem to like either the -- or the
extra set of brackets... it's running on Excel 2000...

But before you expend wasted effort, the larger issue is the
automatic
update of links. Here's the current process...
Techs enter their times on the Timesheet workbook which is colocated
in a folder with their Payroll Master workbook. They are SUPPOSED to
open their Payroll Master to verify that everything is right, but
they generally don't - which means that the values in the Payroll
Master don't update.
Each tech has their own folder. In a separate folder I have the
Payroll Entry/Paycheck Calculator workbook that Managers and
Accounting have access to to enter Additional time, PTO, ect. which
populates cells on each tech's Payroll Master. It also uses the
Payroll Master as a source so that they have easy viewing of
pertinent information without opening each tech's Payroll Master. One
final note, Each Payroll Master is named the same (which I can change
if necessary - I know you can't have multiple files open with the
same name).
The issue here is that the techs will enter their info but not update
the links on the Payroll Master. When Accounting and Managers go to
enter their data, the reference data is not updated because the
Payroll Master hasn't updated.
I hope this hasn't totally lost you, and yes I'm sure there was a
better way of constructing this whole thing, but I started out not
knowing anything about Excel a year ago...
I'm guessing there's a macro that can autoupdated all related links
thru multiple workbooks?
On another note, for some odd reason the links in Payroll Master keep
losing connection to Payroll Entry. Every time I open Payroll Master
and update links (on opening) it says it can't find "Payroll Entry"
and I have to manually tell it where to go. Any suggestions?
On final note, I'll send the workbooks involved if you want, just
give me an address to send to...

"Frank Kabel" wrote:

Hi
post your current COUNTIF formula. It is then very simple to convert
it to a working SUMPRODUCT formula. e.g.

=COUNTIF('c:\temp\[testbook.xls].sheet'!A1:A100,"condition")

should be converted to:
=SUMPRODUCT(--('c:\temp\[testbook.xls].sheet'!A1:A100="condition"))


--
Regards
Frank Kabel
Frankfurt, Germany


pkley wrote:
I have multiple workbooks with multiple sheets (don't ask...)
Anyway, I have a paycheck calculator that does all the math for
OT/DT etc. that works great.
However, if the tech is on call, he marks a X in the On Call box
which in turn makes another workbook cell say Yes.
In my third paycheck workbook I'm using a Countif "yes" to total
the
number of days the tech was on call and then multiply that by the
on
call pay. This is the only link that doesn't work and I recall
reading that countif doesn't work if the source isn't open (which
is
generally the case).
Any suggestions? A macro? A different tactic?
The dates are in one column, and the "Yes" are in another column.
Possibly returning a number instead of yes?
Help! Sick and brain quit working!

I'd try it again.


pkley wrote:

Sorry, already tried that. The description in help (excel 2000) states that
sumproduct treats non numeric values as 0. Don't think sumproduct is the
answer...

"Frank Kabel" wrote:

Hi
for your formula use:
=sumproduct(--('C:\Documents
and Settings\Peter\My
Documents\PRK\[Payroll_Master_Redding_2004.XLS]OCT
04'!$B$8:$B$22="YES"))

--
Regards
Frank Kabel
Frankfurt, Germany


pkley wrote:
Ok, here's the current formula, but I'm pretty sure I've uncovered a
larger issue, which may involve a macro I think...
=COUNTIF('C:\Documents and Settings\Peter\My
Documents\PRK\[Payroll_Master_Redding_2004.XLS]OCT
04'!$B$8:$B$22,"YES") I've tried changing it to what you suggested,
=sumproduct(--('C:\Documents and Settings\Peter\My
Documents\PRK\[Payroll_Master_Redding_2004.XLS]OCT
04'!$B$8:$B$22,"YES")) - it didn't seem to like either the -- or the
extra set of brackets... it's running on Excel 2000...

But before you expend wasted effort, the larger issue is the
automatic
update of links. Here's the current process...
Techs enter their times on the Timesheet workbook which is colocated
in a folder with their Payroll Master workbook. They are SUPPOSED to
open their Payroll Master to verify that everything is right, but
they generally don't - which means that the values in the Payroll
Master don't update.
Each tech has their own folder. In a separate folder I have the
Payroll Entry/Paycheck Calculator workbook that Managers and
Accounting have access to to enter Additional time, PTO, ect. which
populates cells on each tech's Payroll Master. It also uses the
Payroll Master as a source so that they have easy viewing of
pertinent information without opening each tech's Payroll Master. One
final note, Each Payroll Master is named the same (which I can change
if necessary - I know you can't have multiple files open with the
same name).
The issue here is that the techs will enter their info but not update
the links on the Payroll Master. When Accounting and Managers go to
enter their data, the reference data is not updated because the
Payroll Master hasn't updated.
I hope this hasn't totally lost you, and yes I'm sure there was a
better way of constructing this whole thing, but I started out not
knowing anything about Excel a year ago...
I'm guessing there's a macro that can autoupdated all related links
thru multiple workbooks?
On another note, for some odd reason the links in Payroll Master keep
losing connection to Payroll Entry. Every time I open Payroll Master
and update links (on opening) it says it can't find "Payroll Entry"
and I have to manually tell it where to go. Any suggestions?
On final note, I'll send the workbooks involved if you want, just
give me an address to send to...

"Frank Kabel" wrote:

Hi
post your current COUNTIF formula. It is then very simple to convert
it to a working SUMPRODUCT formula. e.g.

=COUNTIF('c:\temp\[testbook.xls].sheet'!A1:A100,"condition")

should be converted to:
=SUMPRODUCT(--('c:\temp\[testbook.xls].sheet'!A1:A100="condition"))


--
Regards
Frank Kabel
Frankfurt, Germany


pkley wrote:
I have multiple workbooks with multiple sheets (don't ask...)
Anyway, I have a paycheck calculator that does all the math for
OT/DT etc. that works great.
However, if the tech is on call, he marks a X in the On Call box
which in turn makes another workbook cell say Yes.
In my third paycheck workbook I'm using a Countif "yes" to total
the
number of days the tech was on call and then multiply that by the
on
call pay. This is the only link that doesn't work and I recall
reading that countif doesn't work if the source isn't open (which
is
generally the case).
Any suggestions? A macro? A different tactic?
The dates are in one column, and the "Yes" are in another column.
Possibly returning a number instead of yes?
Help! Sick and brain quit working!



--

Dave Peterson