I am working on a spreadsheet to keep track of working and non working desks
in an office.

Each desk has 1 row, each piece of equipment has their own column.

I want to have a formula where it will count a station is it has one of the
following "broken usb keyboard" or "broken usb keyboard" or "broken phone" or
"broken monitor". If a station has 2 things broken I want it only counted
once.

Can someone please help me with a formula.

Assume data in row2 down, where col A = desk#
and "broken ... " could appear in either cols B, C or D

Place in E2:
= --(SUMPRODUCT(--(ISNUMBER(SEARCH("broken",B2:D2))))0)
Copy down to the last row. Col E will return a "1" or zero depending on
whether "broken" was found in either cols B, C or D for each desk.

Then you could get a running total of the broken desks
by placing in F2: =SUM($E$2:E2)
and copy F2 down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"FormulaQuestioner" wrote:
I am working on a spreadsheet to keep track of working and non working desks
in an office.

Each desk has 1 row, each piece of equipment has their own column.

I want to have a formula where it will count a station is it has one of the
following "broken usb keyboard" or "broken usb keyboard" or "broken phone" or
"broken monitor". If a station has 2 things broken I want it only counted
once.

Can someone please help me with a formula.

Hi
Try
=COUNTIF(A2:H2,<"")1

Change the range A2:H2 to suit.
Copy down as far as required.
Filter on column with formula=TRUE to find rows which match


--

Regards
Roger Govier

"FormulaQuestioner" wrote in
message ...
I am working on a spreadsheet to keep track of working and non working
desks
in an office.

Each desk has 1 row, each piece of equipment has their own column.

I want to have a formula where it will count a station is it has one of
the
following "broken usb keyboard" or "broken usb keyboard" or "broken phone"
or
"broken monitor". If a station has 2 things broken I want it only counted
once.

Can someone please help me with a formula.

For the site each piece of equipment can have different things wrong with it.
Like it could be "monitor base broken" "monitor screen cracked" for monitors
and for each piece of equipment they can have differnt things like that.
Unfornutaly all of the different equipment will not have the same word in the
description.


"Max" wrote:

Assume data in row2 down, where col A = desk#
and "broken ... " could appear in either cols B, C or D

Place in E2:
= --(SUMPRODUCT(--(ISNUMBER(SEARCH("broken",B2:D2))))0)
Copy down to the last row. Col E will return a "1" or zero depending on
whether "broken" was found in either cols B, C or D for each desk.

Then you could get a running total of the broken desks
by placing in F2: =SUM($E$2:E2)
and copy F2 down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"FormulaQuestioner" wrote:
I am working on a spreadsheet to keep track of working and non working desks
in an office.

Each desk has 1 row, each piece of equipment has their own column.

I want to have a formula where it will count a station is it has one of the
following "broken usb keyboard" or "broken usb keyboard" or "broken phone" or
"broken monitor". If a station has 2 things broken I want it only counted
once.

Can someone please help me with a formula.

You could try replacing the formula in E2 with something like this:
= --(SUMPRODUCT(--(ISNUMBER(SEARCH({"broke";"crack";"spoil"},B2:D2)) ))0)

In the above, I've included 3 possible generic words in this part:
{"broke";"crack";"spoil"}

You could extend the list above to suit other possibilities in your data.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"FormulaQuestioner" wrote:
For the site each piece of equipment can have different things wrong with it.
Like it could be "monitor base broken" "monitor screen cracked" for monitors
and for each piece of equipment they can have differnt things like that.
Unfornutaly all of the different equipment will not have the same word in the
description.

Here are some of the ones that I currently use

Monitor Missing, Monitor Broken, USB Keyboard Damaged, Has PS2 Keyboard,
Phone Broken, Wires peeled

"Max" wrote:

You could try replacing the formula in E2 with something like this:
= --(SUMPRODUCT(--(ISNUMBER(SEARCH({"broke";"crack";"spoil"},B2:D2)) ))0)

In the above, I've included 3 possible generic words in this part:
{"broke";"crack";"spoil"}

You could extend the list above to suit other possibilities in your data.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"FormulaQuestioner" wrote:
For the site each piece of equipment can have different things wrong with it.
Like it could be "monitor base broken" "monitor screen cracked" for monitors
and for each piece of equipment they can have differnt things like that.
Unfornutaly all of the different equipment will not have the same word in the
description.

You could add the core words in and try it like this in E2, then:
=
--(SUMPRODUCT(--(ISNUMBER(SEARCH({"broke";"crack";"spoil";"miss";" peel";"ps2"},B2:D2))))0)

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"FormulaQuestioner" wrote:
Here are some of the ones that I currently use

Monitor Missing, Monitor Broken, USB Keyboard Damaged, Has PS2 Keyboard,
Phone Broken, Wires peeled

Sorry,

I obviously clicked the wrong posting prior to typing the previous posting.
It obviously has nothing at all to do with it.

--

Regards
Roger Govier

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi
Try
=COUNTIF(A2:H2,<"")1

Change the range A2:H2 to suit.
Copy down as far as required.
Filter on column with formula=TRUE to find rows which match


--

Regards
Roger Govier

"FormulaQuestioner" wrote in
message ...
I am working on a spreadsheet to keep track of working and non working
desks
in an office.

Each desk has 1 row, each piece of equipment has their own column.

I want to have a formula where it will count a station is it has one of
the
following "broken usb keyboard" or "broken usb keyboard" or "broken
phone" or
"broken monitor". If a station has 2 things broken I want it only counted
once.

Can someone please help me with a formula.