| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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). |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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). |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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). |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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). |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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). |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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). |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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). |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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). |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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). |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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). |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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). |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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). |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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). |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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). |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to create a cycle count report | Excel Worksheet Functions | |||
| How to create list and count from a table? | New Users to Excel | |||
| How do I create a spreadsheet that will count up? | Excel Discussion (Misc queries) | |||
| How do I create a spreadsheet that will count up? | Excel Discussion (Misc queries) | |||
| Can I create a cell that will count by one every time I print the. | Excel Worksheet Functions | |||
Linear Mode
