Problem with an "if" formula
 

Ok I'm trying to make a formula that says if cells C5-56 (C5:C56)
have an "X" in them, then count the number of x's in cell G5:G56.
I want the total number of x's in the range G5 through G56 IF the
corrosponding
cells in C5:C56 have an X.

This is a list of beds (in a hospital) that are a certain type of bed and if
they are salvageable. (they're broken)
So C5-56 are marked with an x IF the bed is a Total Care bed.
E5-E56 are marked with an x IF the bed is a Zone Air bed.
G5-G56 are marked with an X IF the bed is salvageable.
I need the number of salvageable beds for the Total Cares and for the
Zoneairs and for those that are neither(these would nOT have an x in either
c5-56 NOR G5-56) So I need three different formulas.
Yikes... I hope this makes sense

Problem with an "if" formula
 

Try:

=SUMPRODUCT(--(C5:C56="X"),--(G5:G56="X"))
--
Gary's Student
gsnu200701


"Meenie" wrote:

Ok I'm trying to make a formula that says if cells C5-56 (C5:C56)
have an "X" in them, then count the number of x's in cell G5:G56.
I want the total number of x's in the range G5 through G56 IF the
corrosponding
cells in C5:C56 have an X.

This is a list of beds (in a hospital) that are a certain type of bed and if
they are salvageable. (they're broken)
So C5-56 are marked with an x IF the bed is a Total Care bed.
E5-E56 are marked with an x IF the bed is a Zone Air bed.
G5-G56 are marked with an X IF the bed is salvageable.
I need the number of salvageable beds for the Total Cares and for the
Zoneairs and for those that are neither(these would nOT have an x in either
c5-56 NOR G5-56) So I need three different formulas.
Yikes... I hope this makes sense

Problem with an "if" formula
 

Thanks!
This works great for totaling the two types of beds, but I can't seem to get
it to work for cells that are empty (ie: the bed is a regular bed, not total
care or zoneaire, so neither of those columns are x'd)
In other words, I want it to add the x's in G5:G56 for the cells in C and E
that are blank.
"Gary''s Student" wrote:

Try:

=SUMPRODUCT(--(C5:C56="X"),--(G5:G56="X"))
--
Gary's Student
gsnu200701


"Meenie" wrote:

Ok I'm trying to make a formula that says if cells C5-56 (C5:C56)
have an "X" in them, then count the number of x's in cell G5:G56.
I want the total number of x's in the range G5 through G56 IF the
corrosponding
cells in C5:C56 have an X.

This is a list of beds (in a hospital) that are a certain type of bed and if
they are salvageable. (they're broken)
So C5-56 are marked with an x IF the bed is a Total Care bed.
E5-E56 are marked with an x IF the bed is a Zone Air bed.
G5-G56 are marked with an X IF the bed is salvageable.
I need the number of salvageable beds for the Total Cares and for the
Zoneairs and for those that are neither(these would nOT have an x in either
c5-56 NOR G5-56) So I need three different formulas.
Yikes... I hope this makes sense

Problem with an "if" formula
 

Do BOTH C & E have to be blank to allow a count? Or shall we count if EITHER
C or E is blank??
--
Gary's Student
gsnu200701


"Meenie" wrote:

Thanks!
This works great for totaling the two types of beds, but I can't seem to get
it to work for cells that are empty (ie: the bed is a regular bed, not total
care or zoneaire, so neither of those columns are x'd)
In other words, I want it to add the x's in G5:G56 for the cells in C and E
that are blank.
"Gary''s Student" wrote:

Try:

=SUMPRODUCT(--(C5:C56="X"),--(G5:G56="X"))
--
Gary's Student
gsnu200701


"Meenie" wrote:

Ok I'm trying to make a formula that says if cells C5-56 (C5:C56)
have an "X" in them, then count the number of x's in cell G5:G56.
I want the total number of x's in the range G5 through G56 IF the
corrosponding
cells in C5:C56 have an X.

This is a list of beds (in a hospital) that are a certain type of bed and if
they are salvageable. (they're broken)
So C5-56 are marked with an x IF the bed is a Total Care bed.
E5-E56 are marked with an x IF the bed is a Zone Air bed.
G5-G56 are marked with an X IF the bed is salvageable.
I need the number of salvageable beds for the Total Cares and for the
Zoneairs and for those that are neither(these would nOT have an x in either
c5-56 NOR G5-56) So I need three different formulas.
Yikes... I hope this makes sense

Problem with an "if" formula
 

BOTH have to be blank. :)

"Gary''s Student" wrote:

Do BOTH C & E have to be blank to allow a count? Or shall we count if EITHER
C or E is blank??
--
Gary's Student
gsnu200701


"Meenie" wrote:

Thanks!
This works great for totaling the two types of beds, but I can't seem to get
it to work for cells that are empty (ie: the bed is a regular bed, not total
care or zoneaire, so neither of those columns are x'd)
In other words, I want it to add the x's in G5:G56 for the cells in C and E
that are blank.
"Gary''s Student" wrote:

Try:

=SUMPRODUCT(--(C5:C56="X"),--(G5:G56="X"))
--
Gary's Student
gsnu200701


"Meenie" wrote:

Ok I'm trying to make a formula that says if cells C5-56 (C5:C56)
have an "X" in them, then count the number of x's in cell G5:G56.
I want the total number of x's in the range G5 through G56 IF the
corrosponding
cells in C5:C56 have an X.

This is a list of beds (in a hospital) that are a certain type of bed and if
they are salvageable. (they're broken)
So C5-56 are marked with an x IF the bed is a Total Care bed.
E5-E56 are marked with an x IF the bed is a Zone Air bed.
G5-G56 are marked with an X IF the bed is salvageable.
I need the number of salvageable beds for the Total Cares and for the
Zoneairs and for those that are neither(these would nOT have an x in either
c5-56 NOR G5-56) So I need three different formulas.
Yikes... I hope this makes sense

Problem with an "if" formula
 

EURIKA!!!!!!!!! I got it myself!!
Mine's probably not as sophisticated as it might be, but
=SUMPRODUCT(--(C5:C56="")*(E5:E56=""),--(G5:G56="X")) works!!! <G hehehe

"Gary''s Student" wrote:

Do BOTH C & E have to be blank to allow a count? Or shall we count if EITHER
C or E is blank??
--
Gary's Student
gsnu200701


"Meenie" wrote:

Thanks!
This works great for totaling the two types of beds, but I can't seem to get
it to work for cells that are empty (ie: the bed is a regular bed, not total
care or zoneaire, so neither of those columns are x'd)
In other words, I want it to add the x's in G5:G56 for the cells in C and E
that are blank.
"Gary''s Student" wrote:

Try:

=SUMPRODUCT(--(C5:C56="X"),--(G5:G56="X"))
--
Gary's Student
gsnu200701


"Meenie" wrote:

Ok I'm trying to make a formula that says if cells C5-56 (C5:C56)
have an "X" in them, then count the number of x's in cell G5:G56.
I want the total number of x's in the range G5 through G56 IF the
corrosponding
cells in C5:C56 have an X.

This is a list of beds (in a hospital) that are a certain type of bed and if
they are salvageable. (they're broken)
So C5-56 are marked with an x IF the bed is a Total Care bed.
E5-E56 are marked with an x IF the bed is a Zone Air bed.
G5-G56 are marked with an X IF the bed is salvageable.
I need the number of salvageable beds for the Total Cares and for the
Zoneairs and for those that are neither(these would nOT have an x in either
c5-56 NOR G5-56) So I need three different formulas.
Yikes... I hope this makes sense

Problem with an "if" formula
 

=SUMPRODUCT(--(C5:C56=""),--(G5:G56="X"),--(E5:E56=""))
--
Gary's Student
gsnu200701


"Meenie" wrote:

BOTH have to be blank. :)

"Gary''s Student" wrote:

Do BOTH C & E have to be blank to allow a count? Or shall we count if EITHER
C or E is blank??
--
Gary's Student
gsnu200701


"Meenie" wrote:

Thanks!
This works great for totaling the two types of beds, but I can't seem to get
it to work for cells that are empty (ie: the bed is a regular bed, not total
care or zoneaire, so neither of those columns are x'd)
In other words, I want it to add the x's in G5:G56 for the cells in C and E
that are blank.
"Gary''s Student" wrote:

Try:

=SUMPRODUCT(--(C5:C56="X"),--(G5:G56="X"))
--
Gary's Student
gsnu200701


"Meenie" wrote:

Ok I'm trying to make a formula that says if cells C5-56 (C5:C56)
have an "X" in them, then count the number of x's in cell G5:G56.
I want the total number of x's in the range G5 through G56 IF the
corrosponding
cells in C5:C56 have an X.

This is a list of beds (in a hospital) that are a certain type of bed and if
they are salvageable. (they're broken)
So C5-56 are marked with an x IF the bed is a Total Care bed.
E5-E56 are marked with an x IF the bed is a Zone Air bed.
G5-G56 are marked with an X IF the bed is salvageable.
I need the number of salvageable beds for the Total Cares and for the
Zoneairs and for those that are neither(these would nOT have an x in either
c5-56 NOR G5-56) So I need three different formulas.
Yikes... I hope this makes sense

Problem with an "if" formula
 

I certainly hope Gary give's you an "A+" :)
Thanks a lot!

"Gary''s Student" wrote:

=SUMPRODUCT(--(C5:C56=""),--(G5:G56="X"),--(E5:E56=""))
--
Gary's Student
gsnu200701


"Meenie" wrote:

BOTH have to be blank. :)

"Gary''s Student" wrote:

Do BOTH C & E have to be blank to allow a count? Or shall we count if EITHER
C or E is blank??
--
Gary's Student
gsnu200701


"Meenie" wrote:

Thanks!
This works great for totaling the two types of beds, but I can't seem to get
it to work for cells that are empty (ie: the bed is a regular bed, not total
care or zoneaire, so neither of those columns are x'd)
In other words, I want it to add the x's in G5:G56 for the cells in C and E
that are blank.
"Gary''s Student" wrote:

Try:

=SUMPRODUCT(--(C5:C56="X"),--(G5:G56="X"))
--
Gary's Student
gsnu200701


"Meenie" wrote:

Ok I'm trying to make a formula that says if cells C5-56 (C5:C56)
have an "X" in them, then count the number of x's in cell G5:G56.
I want the total number of x's in the range G5 through G56 IF the
corrosponding
cells in C5:C56 have an X.

This is a list of beds (in a hospital) that are a certain type of bed and if
they are salvageable. (they're broken)
So C5-56 are marked with an x IF the bed is a Total Care bed.
E5-E56 are marked with an x IF the bed is a Zone Air bed.
G5-G56 are marked with an X IF the bed is salvageable.
I need the number of salvageable beds for the Total Cares and for the
Zoneairs and for those that are neither(these would nOT have an x in either
c5-56 NOR G5-56) So I need three different formulas.
Yikes... I hope this makes sense

Problem with an "if" formula
 

Good Job !

As you can see, SUMPRODUCT() gives us the ability to apply many different
criteria at the same time.
--
Gary's Student
gsnu200701


"Meenie" wrote:

EURIKA!!!!!!!!! I got it myself!!
Mine's probably not as sophisticated as it might be, but
=SUMPRODUCT(--(C5:C56="")*(E5:E56=""),--(G5:G56="X")) works!!! <G hehehe

"Gary''s Student" wrote:

Do BOTH C & E have to be blank to allow a count? Or shall we count if EITHER
C or E is blank??
--
Gary's Student
gsnu200701


"Meenie" wrote:

Thanks!
This works great for totaling the two types of beds, but I can't seem to get
it to work for cells that are empty (ie: the bed is a regular bed, not total
care or zoneaire, so neither of those columns are x'd)
In other words, I want it to add the x's in G5:G56 for the cells in C and E
that are blank.
"Gary''s Student" wrote:

Try:

=SUMPRODUCT(--(C5:C56="X"),--(G5:G56="X"))
--
Gary's Student
gsnu200701


"Meenie" wrote:

Ok I'm trying to make a formula that says if cells C5-56 (C5:C56)
have an "X" in them, then count the number of x's in cell G5:G56.
I want the total number of x's in the range G5 through G56 IF the
corrosponding
cells in C5:C56 have an X.

This is a list of beds (in a hospital) that are a certain type of bed and if
they are salvageable. (they're broken)
So C5-56 are marked with an x IF the bed is a Total Care bed.
E5-E56 are marked with an x IF the bed is a Zone Air bed.
G5-G56 are marked with an X IF the bed is salvageable.
I need the number of salvageable beds for the Total Cares and for the
Zoneairs and for those that are neither(these would nOT have an x in either
c5-56 NOR G5-56) So I need three different formulas.
Yikes... I hope this makes sense

Problem with an "if" formula
 

Meenie wrote:
Ok I'm trying to make a formula that says if cells C5-56 (C5:C56)
have an "X" in them, then count the number of x's in cell G5:G56.
I want the total number of x's in the range G5 through G56 IF the
corrosponding
cells in C5:C56 have an X.

This is a list of beds (in a hospital) that are a certain type of bed and if
they are salvageable. (they're broken)
So C5-56 are marked with an x IF the bed is a Total Care bed.
E5-E56 are marked with an x IF the bed is a Zone Air bed.
G5-G56 are marked with an X IF the bed is salvageable.
I need the number of salvageable beds for the Total Cares and for the
Zoneairs and for those that are neither(these would nOT have an x in either
c5-56 NOR G5-56) So I need three different formulas.
Yikes... I hope this makes sense

I used COUNTIF(B:B,"MOW") To count the number of times I 'Mowed'.You
count the number of 'X's' or 'XX's' using this formula.