Create a function to add one to the count

  • Thread starter Thread starter Lawrence
  • Start date Start date
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).
 
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.
 
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
 
Have you tried using =SUM(D2:D22) where D2:D22 is the range of the numbers
to be added together (summed)
best wishes
 
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 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, 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.
 
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?
 
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...
 
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
 
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!!!
 
Back
Top