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

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

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

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

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

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

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