Sumproduct of cells
 

I am currently working on an inventory spreadsheet.

Each row will represent one desk. Each column will be used for a piece of
equipment.

In this spreadsheet each piece of equipment have have things wrong with them
that will make the station count as not working, but some of the entires will
not count towards that.

I would like a formula that I can use in a cell that will count all of the
rows and this is the criteria

problem stations
=all of the cells that have at least one of the following criteris (ie
Montior Broken, Computer no power, USB Mouse Missing)

I did have a formula before that was kind of like that but when ever a field
would have something in it that did not fall into that category it would not
could the station is having a problem

Any help would be appreciated

Sumproduct of cells
 

=SUM(COUNTIF(A1:Z50,{"Monitor Broken","Computer No Power","USB Mouse
Missing"}))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"FormulaQuestioner" wrote in
message ...
I am currently working on an inventory spreadsheet.

Each row will represent one desk. Each column will be used for a piece of
equipment.

In this spreadsheet each piece of equipment have have things wrong with
them
that will make the station count as not working, but some of the entires
will
not count towards that.

I would like a formula that I can use in a cell that will count all of the
rows and this is the criteria

problem stations
=all of the cells that have at least one of the following criteris (ie
Montior Broken, Computer no power, USB Mouse Missing)

I did have a formula before that was kind of like that but when ever a
field
would have something in it that did not fall into that category it would
not
could the station is having a problem

Any help would be appreciated

Sumproduct of cells
 

On Jan 29, 9:10 am, "Bob Phillips" wrote:
=SUM(COUNTIF(A1:Z50,{"Monitor Broken","Computer No Power","USB Mouse
Missing"}))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"FormulaQuestioner" wrote in
...

I am currently working on an inventory spreadsheet.

Each row will represent one desk. Each column will be used for a piece of
equipment.

In this spreadsheet each piece of equipment have have things wrong with
them
that will make the station count as not working, but some of the entires
will
not count towards that.

I would like a formula that I can use in a cell that will count all of the
rows and this is the criteria

problem stations
=all of the cells that have at least one of the following criteris (ie
Montior Broken, Computer no power, USB Mouse Missing)

I did have a formula before that was kind of like that but when ever a
field
would have something in it that did not fall into that category it would
not
could the station is having a problem

Any help would be appreciated

Ignore my earlier response to this - I was having a dumb moment!!

OzGrid has a few other alternatives which can be useful too:-

http://www.ozgrid.com/Excel/count-if.htm

Regards,
Matt Richardson
http://teachr.blogspot.com

Sumproduct of cells
 

Will that work if one row has multiple problems with it. I am doing a count
where I want it to say that there is 1 broken station even if there is
multiple broken equipment there.

"Matt Richardson" wrote:

On Jan 29, 9:10 am, "Bob Phillips" wrote:
=SUM(COUNTIF(A1:Z50,{"Monitor Broken","Computer No Power","USB Mouse
Missing"}))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"FormulaQuestioner" wrote in
...

I am currently working on an inventory spreadsheet.

Each row will represent one desk. Each column will be used for a piece of
equipment.

In this spreadsheet each piece of equipment have have things wrong with
them
that will make the station count as not working, but some of the entires
will
not count towards that.

I would like a formula that I can use in a cell that will count all of the
rows and this is the criteria

problem stations
=all of the cells that have at least one of the following criteris (ie
Montior Broken, Computer no power, USB Mouse Missing)

I did have a formula before that was kind of like that but when ever a
field
would have something in it that did not fall into that category it would
not
could the station is having a problem

Any help would be appreciated

Ignore my earlier response to this - I was having a dumb moment!!

OzGrid has a few other alternatives which can be useful too:-

http://www.ozgrid.com/Excel/count-if.htm

Regards,
Matt Richardson
http://teachr.blogspot.com

Sumproduct of cells
 

When I first made this chart last year I was given a SUMProduct formula that
looks like it had me say "sumproduct (keyboard cell does not contain words
meaning that the station is not broken0,(monitor cell does not contain one of
the words meaning that the station is not broken)

"FormulaQuestioner" wrote:

Will that work if one row has multiple problems with it. I am doing a count
where I want it to say that there is 1 broken station even if there is
multiple broken equipment there.

"Matt Richardson" wrote:

On Jan 29, 9:10 am, "Bob Phillips" wrote:
=SUM(COUNTIF(A1:Z50,{"Monitor Broken","Computer No Power","USB Mouse
Missing"}))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"FormulaQuestioner" wrote in
...

I am currently working on an inventory spreadsheet.

Each row will represent one desk. Each column will be used for a piece of
equipment.

In this spreadsheet each piece of equipment have have things wrong with
them
that will make the station count as not working, but some of the entires
will
not count towards that.

I would like a formula that I can use in a cell that will count all of the
rows and this is the criteria

problem stations
=all of the cells that have at least one of the following criteris (ie
Montior Broken, Computer no power, USB Mouse Missing)

I did have a formula before that was kind of like that but when ever a
field
would have something in it that did not fall into that category it would
not
could the station is having a problem

Any help would be appreciated

Ignore my earlier response to this - I was having a dumb moment!!

OzGrid has a few other alternatives which can be useful too:-

http://www.ozgrid.com/Excel/count-if.htm

Regards,
Matt Richardson
http://teachr.blogspot.com

Sumproduct of cells
 

It should, but why don't you just try and see, and report back if you need
any changes.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"FormulaQuestioner" wrote in
message ...
Will that work if one row has multiple problems with it. I am doing a
count
where I want it to say that there is 1 broken station even if there is
multiple broken equipment there.

"Matt Richardson" wrote:

On Jan 29, 9:10 am, "Bob Phillips" wrote:
=SUM(COUNTIF(A1:Z50,{"Monitor Broken","Computer No Power","USB Mouse
Missing"}))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"FormulaQuestioner" wrote
in
...

I am currently working on an inventory spreadsheet.

Each row will represent one desk. Each column will be used for a
piece of
equipment.

In this spreadsheet each piece of equipment have have things wrong
with
them
that will make the station count as not working, but some of the
entires
will
not count towards that.

I would like a formula that I can use in a cell that will count all
of the
rows and this is the criteria

problem stations
=all of the cells that have at least one of the following criteris
(ie
Montior Broken, Computer no power, USB Mouse Missing)

I did have a formula before that was kind of like that but when ever
a
field
would have something in it that did not fall into that category it
would
not
could the station is having a problem

Any help would be appreciated

Ignore my earlier response to this - I was having a dumb moment!!

OzGrid has a few other alternatives which can be useful too:-

http://www.ozgrid.com/Excel/count-if.htm

Regards,
Matt Richardson
http://teachr.blogspot.com

Sumproduct of cells
 

I tried that and it seems to count each time in the row where one of those
criteria is.

I would like it to be that if 1 row has multiple things (like Monitor
Broken, Keyboard Broekn) the count would only add 1 to the total because that
row was already counted as having something broken

"Bob Phillips" wrote:

It should, but why don't you just try and see, and report back if you need
any changes.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"FormulaQuestioner" wrote in
message ...
Will that work if one row has multiple problems with it. I am doing a
count
where I want it to say that there is 1 broken station even if there is
multiple broken equipment there.

"Matt Richardson" wrote:

On Jan 29, 9:10 am, "Bob Phillips" wrote:
=SUM(COUNTIF(A1:Z50,{"Monitor Broken","Computer No Power","USB Mouse
Missing"}))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"FormulaQuestioner" wrote
in
...

I am currently working on an inventory spreadsheet.

Each row will represent one desk. Each column will be used for a
piece of
equipment.

In this spreadsheet each piece of equipment have have things wrong
with
them
that will make the station count as not working, but some of the
entires
will
not count towards that.

I would like a formula that I can use in a cell that will count all
of the
rows and this is the criteria

problem stations
=all of the cells that have at least one of the following criteris
(ie
Montior Broken, Computer no power, USB Mouse Missing)

I did have a formula before that was kind of like that but when ever
a
field
would have something in it that did not fall into that category it
would
not
could the station is having a problem

Any help would be appreciated

Ignore my earlier response to this - I was having a dumb moment!!

OzGrid has a few other alternatives which can be useful too:-

http://www.ozgrid.com/Excel/count-if.htm

Regards,
Matt Richardson
http://teachr.blogspot.com

Sumproduct of cells
 

Why not just reduce the formula and add

=SUM(COUNTIF(A1:Z1,{"Monitor Broken","Computer No Power","USB Mouse
Missing"}))

to a spare column in row 1, copy that down, and sum that column

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"FormulaQuestioner" wrote in
message ...
I tried that and it seems to count each time in the row where one of those
criteria is.

I would like it to be that if 1 row has multiple things (like Monitor
Broken, Keyboard Broekn) the count would only add 1 to the total because
that
row was already counted as having something broken

"Bob Phillips" wrote:

It should, but why don't you just try and see, and report back if you
need
any changes.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"FormulaQuestioner" wrote
in
message ...
Will that work if one row has multiple problems with it. I am doing a
count
where I want it to say that there is 1 broken station even if there is
multiple broken equipment there.

"Matt Richardson" wrote:

On Jan 29, 9:10 am, "Bob Phillips" wrote:
=SUM(COUNTIF(A1:Z50,{"Monitor Broken","Computer No Power","USB Mouse
Missing"}))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)

"FormulaQuestioner"
wrote
in
...

I am currently working on an inventory spreadsheet.

Each row will represent one desk. Each column will be used for a
piece of
equipment.

In this spreadsheet each piece of equipment have have things wrong
with
them
that will make the station count as not working, but some of the
entires
will
not count towards that.

I would like a formula that I can use in a cell that will count
all
of the
rows and this is the criteria

problem stations
=all of the cells that have at least one of the following criteris
(ie
Montior Broken, Computer no power, USB Mouse Missing)

I did have a formula before that was kind of like that but when
ever
a
field
would have something in it that did not fall into that category it
would
not
could the station is having a problem

Any help would be appreciated

Ignore my earlier response to this - I was having a dumb moment!!

OzGrid has a few other alternatives which can be useful too:-

http://www.ozgrid.com/Excel/count-if.htm

Regards,
Matt Richardson
http://teachr.blogspot.com

Sumproduct of cells
 

I was thinking about that. But was hoping to have one formula.

When I first asked the question on here I was told to use this formula.

=SUMPRODUCT(--(chmProMonitor<"Pixels Damaged"),--(chmProMonitor<"Low
Resolution"),--(chmProKeyboard<"USB Damaged"),--(chmProKeyboard<"PS2
Damaged"),--(chmProKeyboard<"PS2 Keyboard"),--(chmProMouse<"USB
Broken"),--(chmProMouse<"PS2
Broken"),--(chmProWinterm<"Cracked"),--(chmProPhone<"Pixels
Damaged"),--(chmProHeadset<"Damaged"),--(chmProHeadset<"Not
Needed"),--(chmProEthernet<"Detached"),--(chmProEthernet<"Not On
Network"),--(chmProPhonejack<"Detached"))-SUMPRODUCT((chmProMonitor="")*(chmProKeyboard="")* (chmProMouse="")*(chmProWinterm="")*(chmProPhone=" ")*(chmProHeadset="")*(chmProEthernet="")*(chmProP honejack=""))

The chmPro words are defined names that I used so it was easier when I had
to type out that range number of cells again.

I am get the problem where if the chmProMonitor is marked broken it will
count as 1 in the cell that I have that formula in. But is chmProMonitor says
"Broken" and chmProPhone is "Not Needed" (which that option does not count as
a problem with the station) the number in the field that I have the formula
in will not count. It will just be 0

Sorry if that was confusing, I am not good at explaining things at times

"Bob Phillips" wrote:

Why not just reduce the formula and add

=SUM(COUNTIF(A1:Z1,{"Monitor Broken","Computer No Power","USB Mouse
Missing"}))

to a spare column in row 1, copy that down, and sum that column

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"FormulaQuestioner" wrote in
message ...
I tried that and it seems to count each time in the row where one of those
criteria is.

I would like it to be that if 1 row has multiple things (like Monitor
Broken, Keyboard Broekn) the count would only add 1 to the total because
that
row was already counted as having something broken

"Bob Phillips" wrote:

It should, but why don't you just try and see, and report back if you
need
any changes.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"FormulaQuestioner" wrote
in
message ...
Will that work if one row has multiple problems with it. I am doing a
count
where I want it to say that there is 1 broken station even if there is
multiple broken equipment there.

"Matt Richardson" wrote:

On Jan 29, 9:10 am, "Bob Phillips" wrote:
=SUM(COUNTIF(A1:Z50,{"Monitor Broken","Computer No Power","USB Mouse
Missing"}))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)

"FormulaQuestioner"
wrote
in
...

I am currently working on an inventory spreadsheet.

Each row will represent one desk. Each column will be used for a
piece of
equipment.

In this spreadsheet each piece of equipment have have things wrong
with
them
that will make the station count as not working, but some of the
entires
will
not count towards that.

I would like a formula that I can use in a cell that will count
all
of the
rows and this is the criteria

problem stations
=all of the cells that have at least one of the following criteris
(ie
Montior Broken, Computer no power, USB Mouse Missing)

I did have a formula before that was kind of like that but when
ever
a
field
would have something in it that did not fall into that category it
would
not
could the station is having a problem

Any help would be appreciated

Ignore my earlier response to this - I was having a dumb moment!!

OzGrid has a few other alternatives which can be useful too:-

http://www.ozgrid.com/Excel/count-if.htm

Regards,
Matt Richardson
http://teachr.blogspot.com

Sumproduct of cells
 

I worked with the Sum(countif) code that I was given earlier in this post and
have it mostly working the way I would like it. How do I add the line so it
will say "Count if it matches one of the "Missing","Broken","* Missing","No
Power","Liscense","Needs to be reflashed" AND does not say "Not on Network"

=SUM(COUNTIF(F226:M226,{"Missing","Broken","* Missing","No
Power","Liscense","Needs to be reflashed"}))

"FormulaQuestioner" wrote:

I was thinking about that. But was hoping to have one formula.

When I first asked the question on here I was told to use this formula.

=SUMPRODUCT(--(chmProMonitor<"Pixels Damaged"),--(chmProMonitor<"Low
Resolution"),--(chmProKeyboard<"USB Damaged"),--(chmProKeyboard<"PS2
Damaged"),--(chmProKeyboard<"PS2 Keyboard"),--(chmProMouse<"USB
Broken"),--(chmProMouse<"PS2
Broken"),--(chmProWinterm<"Cracked"),--(chmProPhone<"Pixels
Damaged"),--(chmProHeadset<"Damaged"),--(chmProHeadset<"Not
Needed"),--(chmProEthernet<"Detached"),--(chmProEthernet<"Not On
Network"),--(chmProPhonejack<"Detached"))-SUMPRODUCT((chmProMonitor="")*(chmProKeyboard="")* (chmProMouse="")*(chmProWinterm="")*(chmProPhone=" ")*(chmProHeadset="")*(chmProEthernet="")*(chmProP honejack=""))

The chmPro words are defined names that I used so it was easier when I had
to type out that range number of cells again.

I am get the problem where if the chmProMonitor is marked broken it will
count as 1 in the cell that I have that formula in. But is chmProMonitor says
"Broken" and chmProPhone is "Not Needed" (which that option does not count as
a problem with the station) the number in the field that I have the formula
in will not count. It will just be 0

Sorry if that was confusing, I am not good at explaining things at times

"Bob Phillips" wrote:

Why not just reduce the formula and add

=SUM(COUNTIF(A1:Z1,{"Monitor Broken","Computer No Power","USB Mouse
Missing"}))

to a spare column in row 1, copy that down, and sum that column

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"FormulaQuestioner" wrote in
message ...
I tried that and it seems to count each time in the row where one of those
criteria is.

I would like it to be that if 1 row has multiple things (like Monitor
Broken, Keyboard Broekn) the count would only add 1 to the total because
that
row was already counted as having something broken

"Bob Phillips" wrote:

It should, but why don't you just try and see, and report back if you
need
any changes.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"FormulaQuestioner" wrote
in
message ...
Will that work if one row has multiple problems with it. I am doing a
count
where I want it to say that there is 1 broken station even if there is
multiple broken equipment there.

"Matt Richardson" wrote:

On Jan 29, 9:10 am, "Bob Phillips" wrote:
=SUM(COUNTIF(A1:Z50,{"Monitor Broken","Computer No Power","USB Mouse
Missing"}))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)

"FormulaQuestioner"
wrote
in
...

I am currently working on an inventory spreadsheet.

Each row will represent one desk. Each column will be used for a
piece of
equipment.

In this spreadsheet each piece of equipment have have things wrong
with
them
that will make the station count as not working, but some of the
entires
will
not count towards that.

I would like a formula that I can use in a cell that will count
all
of the
rows and this is the criteria

problem stations
=all of the cells that have at least one of the following criteris
(ie
Montior Broken, Computer no power, USB Mouse Missing)

I did have a formula before that was kind of like that but when
ever
a
field
would have something in it that did not fall into that category it
would
not
could the station is having a problem

Any help would be appreciated

Ignore my earlier response to this - I was having a dumb moment!!

OzGrid has a few other alternatives which can be useful too:-

http://www.ozgrid.com/Excel/count-if.htm

Regards,
Matt Richardson
http://teachr.blogspot.com