Sumproduct question

[John in Wembley]

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

 

[Pete_UK]

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

 

[PCLIVE]

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]

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

 

[Pete_UK]

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

 

[John in Wembley]

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

 

[Pete_UK]

LOL!

Ah well, if you're in a doctor's surgery or hospital, then you might
get something to treat that!! <bg>

Pete