Create a function to add one to the count
 

OK, this is probably an easy one, but I can't figure it out. We (two of us)
keep a list of patients that we put PICCs in, I want to have a small table
automatically total the number Attempts, not attempts, and successful
placements.
so basically, if D3=Y and E3=LR then add one to the number in Attempts (J4)
and successful placements (J6).

Create a function to add one to the count
 

I am actually trying to figure out the same thing. I have a column for Years
of Experience and I want it to add 1 every year.

"Lawrence" wrote:

OK, this is probably an easy one, but I can't figure it out. We (two of us)
keep a list of patients that we put PICCs in, I want to have a small table
automatically total the number Attempts, not attempts, and successful
placements.
so basically, if D3=Y and E3=LR then add one to the number in Attempts (J4)
and successful placements (J6).

Create a function to add one to the count
 

If you are happy to do this manually:
Type 1 in an empty cell;
Copy that cell
Select the column of Years_of_Experience
Use Edit | Paste Special - Add
Now you can delete the 1 in the first cell
Add a Note (using Drawing toolbar) to remind yourself how to do this each
Jan 1.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"sarahmiller15" wrote in message
...
I am actually trying to figure out the same thing. I have a column for
Years
of Experience and I want it to add 1 every year.

"Lawrence" wrote:

OK, this is probably an easy one, but I can't figure it out. We (two of
us)
keep a list of patients that we put PICCs in, I want to have a small
table
automatically total the number Attempts, not attempts, and successful
placements.
so basically, if D3=Y and E3=LR then add one to the number in Attempts
(J4)
and successful placements (J6).

Create a function to add one to the count
 

Have you tried using =SUM(D2:D22) where D2:D22 is the range of the numbers
to be added together (summed)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Lawrence" wrote in message
...
OK, this is probably an easy one, but I can't figure it out. We (two of
us)
keep a list of patients that we put PICCs in, I want to have a small table
automatically total the number Attempts, not attempts, and successful
placements.
so basically, if D3=Y and E3=LR then add one to the number in Attempts
(J4)
and successful placements (J6).

Create a function to add one to the count
 

initially that would have worked but there are two people that can put either
Y, N, or n/a (if we do not attempt at all) in the D column.

"Bernard Liengme" wrote:

Have you tried using =SUM(D2:D22) where D2:D22 is the range of the numbers
to be added together (summed)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Lawrence" wrote in message
...
OK, this is probably an easy one, but I can't figure it out. We (two of
us)
keep a list of patients that we put PICCs in, I want to have a small table
automatically total the number Attempts, not attempts, and successful
placements.
so basically, if D3=Y and E3=LR then add one to the number in Attempts
(J4)
and successful placements (J6).

Create a function to add one to the count
 

=COUNTIF(D2:D22,"Y")
This is not case sensitive so it counts Y and y
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Lawrence" wrote in message
...
initially that would have worked but there are two people that can put
either
Y, N, or n/a (if we do not attempt at all) in the D column.

"Bernard Liengme" wrote:

Have you tried using =SUM(D2:D22) where D2:D22 is the range of the
numbers
to be added together (summed)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Lawrence" wrote in message
...
OK, this is probably an easy one, but I can't figure it out. We (two of
us)
keep a list of patients that we put PICCs in, I want to have a small
table
automatically total the number Attempts, not attempts, and successful
placements.
so basically, if D3=Y and E3=LR then add one to the number in Attempts
(J4)
and successful placements (J6).

Create a function to add one to the count
 

Bernard you have been so helpful I want to thank you. I was thinking that if
I showed you what I was working with and for it would help, so here is a
sample of what the data looks like:
A B C D E
Date Name Room success by
1-Nov john 1 y LR
2-Nov jane 1 y LR
2-Nov bill 1 y LR
2-Nov tony 1 y geo
3-Nov alfred 2 n/a geo
3-Nov horatio 2 n/a LR
5-Nov gene 2 n geo
6-Nov hillary 3 n LR

Here is what the table that I'm trying to auto update looks like. The
countif formula worked for the totals under Y, N and N/A, but I couldn't get
it to work properly for each persons individual subtotals

Attempts Y N N/A
LR
geo
Totals 0 4 1 2

Again Bernard thanks for your help so far.

"Bernard Liengme" wrote:

=COUNTIF(D2:D22,"Y")
This is not case sensitive so it counts Y and y
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Lawrence" wrote in message
...
initially that would have worked but there are two people that can put
either
Y, N, or n/a (if we do not attempt at all) in the D column.

"Bernard Liengme" wrote:

Have you tried using =SUM(D2:D22) where D2:D22 is the range of the
numbers
to be added together (summed)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Lawrence" wrote in message
...
OK, this is probably an easy one, but I can't figure it out. We (two of
us)
keep a list of patients that we put PICCs in, I want to have a small
table
automatically total the number Attempts, not attempts, and successful
placements.
so basically, if D3=Y and E3=LR then add one to the number in Attempts
(J4)
and successful placements (J6).

Create a function to add one to the count
 

OHHH I forgot to let you know, at work I have Excel 2003

"Bernard Liengme" wrote:

=COUNTIF(D2:D22,"Y")
This is not case sensitive so it counts Y and y
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Lawrence" wrote in message
...
initially that would have worked but there are two people that can put
either
Y, N, or n/a (if we do not attempt at all) in the D column.

"Bernard Liengme" wrote:

Have you tried using =SUM(D2:D22) where D2:D22 is the range of the
numbers
to be added together (summed)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Lawrence" wrote in message
...
OK, this is probably an easy one, but I can't figure it out. We (two of
us)
keep a list of patients that we put PICCs in, I want to have a small
table
automatically total the number Attempts, not attempts, and successful
placements.
so basically, if D3=Y and E3=LR then add one to the number in Attempts
(J4)
and successful placements (J6).

Create a function to add one to the count
 

Bernard, you got me looking in the right direction. I found that using
=COUNTIFS(D2:D9,"=*",E2:E9,"=lr") and variations using Y N & N/A as well as
lr & geo will break out the individual subtotals that I want. Thanks so much
for your help in pointing me in the right direction.

"Lawrence" wrote:

OHHH I forgot to let you know, at work I have Excel 2003

"Bernard Liengme" wrote:

=COUNTIF(D2:D22,"Y")
This is not case sensitive so it counts Y and y
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Lawrence" wrote in message
...
initially that would have worked but there are two people that can put
either
Y, N, or n/a (if we do not attempt at all) in the D column.

"Bernard Liengme" wrote:

Have you tried using =SUM(D2:D22) where D2:D22 is the range of the
numbers
to be added together (summed)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Lawrence" wrote in message
...
OK, this is probably an easy one, but I can't figure it out. We (two of
us)
keep a list of patients that we put PICCs in, I want to have a small
table
automatically total the number Attempts, not attempts, and successful
placements.
so basically, if D3=Y and E3=LR then add one to the number in Attempts
(J4)
and successful placements (J6).

Create a function to add one to the count
 

now on the downside............ apparently COUNTIFS(excel2007-home version)
isn't compatible with excel2003 (work version). wonder if I can talk the IS
guys into getting 2007. OR is there some compatible function in 2003?

"Lawrence" wrote:

Bernard, you got me looking in the right direction. I found that using
=COUNTIFS(D2:D9,"=*",E2:E9,"=lr") and variations using Y N & N/A as well as
lr & geo will break out the individual subtotals that I want. Thanks so much
for your help in pointing me in the right direction.

"Lawrence" wrote:

OHHH I forgot to let you know, at work I have Excel 2003

"Bernard Liengme" wrote:

=COUNTIF(D2:D22,"Y")
This is not case sensitive so it counts Y and y
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Lawrence" wrote in message
...
initially that would have worked but there are two people that can put
either
Y, N, or n/a (if we do not attempt at all) in the D column.

"Bernard Liengme" wrote:

Have you tried using =SUM(D2:D22) where D2:D22 is the range of the
numbers
to be added together (summed)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Lawrence" wrote in message
...
OK, this is probably an easy one, but I can't figure it out. We (two of
us)
keep a list of patients that we put PICCs in, I want to have a small
table
automatically total the number Attempts, not attempts, and successful
placements.
so basically, if D3=Y and E3=LR then add one to the number in Attempts
(J4)
and successful placements (J6).

Create a function to add one to the count
 

SUMPRODUCT ?
--
David Biddulph

"Lawrence" wrote in message
...
now on the downside............ apparently COUNTIFS(excel2007-home
version)
isn't compatible with excel2003 (work version). wonder if I can talk the
IS
guys into getting 2007. OR is there some compatible function in 2003?

"Lawrence" wrote:

Bernard, you got me looking in the right direction. I found that using
=COUNTIFS(D2:D9,"=*",E2:E9,"=lr") and variations using Y N & N/A as well
as
lr & geo will break out the individual subtotals that I want. Thanks so
much
for your help in pointing me in the right direction.

"Lawrence" wrote:

OHHH I forgot to let you know, at work I have Excel 2003

"Bernard Liengme" wrote:

=COUNTIF(D2:D22,"Y")
This is not case sensitive so it counts Y and y
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Lawrence" wrote in message
...
initially that would have worked but there are two people that can
put
either
Y, N, or n/a (if we do not attempt at all) in the D column.

"Bernard Liengme" wrote:

Have you tried using =SUM(D2:D22) where D2:D22 is the range of the
numbers
to be added together (summed)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Lawrence" wrote in message
...
OK, this is probably an easy one, but I can't figure it out. We
(two of
us)
keep a list of patients that we put PICCs in, I want to have a
small
table
automatically total the number Attempts, not attempts, and
successful
placements.
so basically, if D3=Y and E3=LR then add one to the number in
Attempts
(J4)
and successful placements (J6).

Create a function to add one to the count
 

On Nov 28, 7:30 pm, Lawrence
wrote:
Bernard you have been so helpful I want to thank you. I was thinking that if
I showed you what I was working with and for it would help, so here is a
sample of what the data looks like:
A B C D E
Date Name Room success by
1-Nov john 1 y LR
2-Nov jane 1 y LR
2-Nov bill 1 y LR
2-Nov tony 1 y geo
3-Nov alfred 2 n/a geo
3-Nov horatio 2 n/a LR
5-Nov gene 2 n geo
6-Nov hillary 3 n LR

Here is what the table that I'm trying to auto update looks like. The
countif formula worked for the totals under Y, N and N/A, but I couldn't get
it to work properly for each persons individual subtotals

Attempts Y N N/A
LR
geo
Totals 0 4 1 2

I'm not sure what the 'Attempts' is as your total is zero. Your Total
for N is 1 but I think should be 2.

My calculation shows
Y N N/A
LR 3 1 1
geo 1 1 1
Totals 4 2 2

Using the formula
=SUM(IF($E$3:$E$10=$A14,IF($D$3:$D$10=C$13,1,0),0) )
which needs to be entered with Ctrl-Shift-Enter
E3:E10 is initials (LR, geo)
A14 is the cell containing "geo" entered as a variable so you can add
more names easily
D3:D10 is Sucess
C13 is Y, N or N/A, entered as a reference so you can easily copy/
paste

Create a function to add one to the count
 

For multiple criteria use SUMPRODUCT. Have a look at:
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html

--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Lawrence" wrote in message
...
OK, this is probably an easy one, but I can't figure it out. We (two of
us)
keep a list of patients that we put PICCs in, I want to have a small table
automatically total the number Attempts, not attempts, and successful
placements.
so basically, if D3=Y and E3=LR then add one to the number in Attempts
(J4)
and successful placements (J6).

Create a function to add one to the count
 

Bernard and David, you have helped me so much, THANKS, you both pointed me in
the right direction, the article that Bernard linked me to gave me what I was
looking for to use with the 2003 version I have at work. Now I know how to do
it with both the 2003 version at work and the 2007 version I have at home.
I ended up using variations of this formula to get what I wanted, it may not
be pretty but it works: this for LR's total of attempts
=SUM(IF(D2:D42="Y",IF(E2:E42="lr",1,0),0)+IF(D2:D4 2="N",IF(E2:E42="LR",1,0),0))

Thanks again!!!
"Lawrence" wrote:

OK, this is probably an easy one, but I can't figure it out. We (two of us)
keep a list of patients that we put PICCs in, I want to have a small table
automatically total the number Attempts, not attempts, and successful
placements.
so basically, if D3=Y and E3=LR then add one to the number in Attempts (J4)
and successful placements (J6).

Create a function to add one to the count
 

Thanks for the feed back.
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Lawrence" wrote in message
...
Bernard and David, you have helped me so much, THANKS, you both pointed me
in
the right direction, the article that Bernard linked me to gave me what I
was
looking for to use with the 2003 version I have at work. Now I know how to
do
it with both the 2003 version at work and the 2007 version I have at
home.
I ended up using variations of this formula to get what I wanted, it may
not
be pretty but it works: this for LR's total of attempts
=SUM(IF(D2:D42="Y",IF(E2:E42="lr",1,0),0)+IF(D2:D4 2="N",IF(E2:E42="LR",1,0),0))

Thanks again!!!
"Lawrence" wrote:

OK, this is probably an easy one, but I can't figure it out. We (two of
us)
keep a list of patients that we put PICCs in, I want to have a small
table
automatically total the number Attempts, not attempts, and successful
placements.
so basically, if D3=Y and E3=LR then add one to the number in Attempts
(J4)
and successful placements (J6).