Running total for inventory

[FormulaQuestioner]

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.

 

[Max]

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",B22))))>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

 

[Roger Govier]

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

 

[FormulaQuestioner]

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]

You could try replacing the formula in E2 with something like this:
= --(SUMPRODUCT(--(ISNUMBER(SEARCH({"broke";"crack";"spoil"},B22))))>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.

 

[FormulaQuestioner]

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]

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

 

[Roger Govier]

Sorry,

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