Create a function to add one to the count

L

Lawrence

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).
 
S

sarahmiller15

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.
 
B

Bernard Liengme

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
 
B

Bernard Liengme

Have you tried using =SUM(D2:D22) where D2:D22 is the range of the numbers
to be added together (summed)
best wishes
 
L

Lawrence

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.
 
B

Bernard Liengme

=COUNTIF(D2:D22,"Y")
This is not case sensitive so it counts Y and y
best wishes
 
L

Lawrence

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.
 
L

Lawrence

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.
 
L

Lawrence

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?
 
D

David Biddulph

SUMPRODUCT ?
--
David Biddulph

Lawrence said:
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?
Click to expand...
 
P

Piscator

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
Click to expand...

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
 
L

Lawrence

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:D42="N",IF(E2:E42="LR",1,0),0))

Thanks again!!!
 
Top