Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct question
Hi team
Ive a sheet at work with the names of patients & wich room they are in. I count the 'census' with sum product: =SUMPRODUCT(--(LEN(A1:A10)=2),--(LEN(A1:A10)<=30)) As you see, I count anything from a patient whos intials only have been entered in the sheet up to their full name. Is there anyway I can exclude counting "guest" if a family member is in one of the patient rooms (the phrase "guest" is entered in the sheet)? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct question
Try this, John:
=SUMPRODUCT(--(LEN(A1:A10)=2),--(LEN(A1:A10)<=30),-- (TRIM(A1:A10)<"guest")) Not sensitive to case, and will take account of any leading or trailing spaces around "guest". Hope this helps. Pete On Jan 29, 7:41*pm, John in Wembley wrote: Hi team Ive a sheet at work with the names of patients & wich room they are in. I count the 'census' with sum product: =SUMPRODUCT(--(LEN(A1:A10)=2),--(LEN(A1:A10)<=30)) As you see, I count anything from a patient whos intials only have been entered in the sheet up to their full name. Is there anyway I can exclude counting "guest" if a family member is in one of the patient rooms (the phrase "guest" is entered in the sheet)? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct question
Where is "guest" entered?
Let's say it is column B...then: =SUMPRODUCT(--(LEN(A1:A10)=2),--(LEN(A1:A10)<=30),--(B1:B10<"guest")) HTH, Paul -- "John in Wembley" wrote in message ... Hi team Ive a sheet at work with the names of patients & wich room they are in. I count the 'census' with sum product: =SUMPRODUCT(--(LEN(A1:A10)=2),--(LEN(A1:A10)<=30)) As you see, I count anything from a patient whos intials only have been entered in the sheet up to their full name. Is there anyway I can exclude counting "guest" if a family member is in one of the patient rooms (the phrase "guest" is entered in the sheet)? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct question
On Tue, 29 Jan 2008 14:49:43 -0500, "PCLIVE"
wrote: Where is "guest" entered? Let's say it is column B...then: =SUMPRODUCT(--(LEN(A1:A10)=2),--(LEN(A1:A10)<=30),--(B1:B10<"guest")) HTH, Paul thanks to both of you, Guest will be in place of the patients name... You would not belive how long some names are! I started out with LEN 15 and as you see, currently up to 30. Kiwi names are shorter! "mate", "gidday mate" etc etc... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct question
Does it matter if the name is very long? Why not just test for length
being greater than 1 (i.e. your first term)? Pete On Jan 29, 8:04*pm, John in Wembley wrote: On Tue, 29 Jan 2008 14:49:43 -0500, "PCLIVE" wrote: Where is "guest" entered? Let's say it is column B...then: =SUMPRODUCT(--(LEN(A1:A10)=2),--(LEN(A1:A10)<=30),--(B1:B10<"guest")) HTH, Paul thanks to both of you, Guest will be in place of the patients name... You would not belive how long some names are! I started out with LEN 15 and as you see, currently up to 30. Kiwi names are shorter! "mate", "gidday mate" etc etc... |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct question
On Tue, 29 Jan 2008 12:10:20 -0800 (PST), Pete_UK
wrote: Does it matter if the name is very long? Why not just test for length being greater than 1 (i.e. your first term)? Thanks Pete, good observation. Im scratching my head for an excuse to why I did that! so far no go, just red patch on scalp. cheers |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct question
LOL!
Ah well, if you're in a doctor's surgery or hospital, then you might get something to treat that!! <bg Pete On Jan 29, 8:13*pm, John in Wembley wrote: On Tue, 29 Jan 2008 12:10:20 -0800 (PST), Pete_UK wrote: Does it matter if the name is very long? Why not just test for length being greater than 1 (i.e. your first term)? Thanks Pete, good observation. Im scratching my head for an excuse to why I did that! so far no go, just red patch on scalp. cheers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct question | Excel Worksheet Functions | |||
Sumproduct question | Excel Worksheet Functions | |||
Sumproduct Question | Excel Discussion (Misc queries) | |||
Sumproduct question | Excel Worksheet Functions | |||
SUMPRODUCT Question | Excel Worksheet Functions |