Complicated counting of cells (based on other cells contents)
 

Hi,

I'm looking to count the number of cells in a column based on the content in
that cell and the content of another cell.

Column 1 has three possible values (GGF, CGF, SGF) as text.
Column 2 has two possible values (Yes, No) as text.
Column 3 has two possible values (Pass, Fail) as text.

I need to be able to make several counts based on the first column,
referencing the other two columns. I'm going to use three different cells to
display each count to simplify the formulas and to ease reading.

Could someone please help me work the formula out to count the number of
cells in column 1 with the value "GGF" when the value of column 2 is "No".
I'd also like some pointers on how to work out how many cells have "GGF" and
"Pass"/"Fail".

The problem I'm hitting at the moment (I think) is that I need to be
flexible (I don't know how many rows are in the column) and using $C:$C (for
instance) seems to be messing up the count.

I've tried:

=COUNT(IF(($C:$C="GGF") & ($G:$G="No), 1, 0))

but that doesn't seem to cut the mustard so to speak.

Any pointers would be a great help to someone in need of some brain soothing
after trying to make Excel do this :-)

Thanks
George

Complicated counting of cells (based on other cells contents)
 

modify as needed to get all your permutations

=SUMPRODUCT(--(A1:A1000="GGF"),--(B1:B1000="No"))

or, for all 3 columns

=SUMPRODUCT(--(A1:A1000="GGF"),--(B1:B1000="No"), --(C1:c1000="Pass"))


"George" wrote:

Hi,

I'm looking to count the number of cells in a column based on the content in
that cell and the content of another cell.

Column 1 has three possible values (GGF, CGF, SGF) as text.
Column 2 has two possible values (Yes, No) as text.
Column 3 has two possible values (Pass, Fail) as text.

I need to be able to make several counts based on the first column,
referencing the other two columns. I'm going to use three different cells to
display each count to simplify the formulas and to ease reading.

Could someone please help me work the formula out to count the number of
cells in column 1 with the value "GGF" when the value of column 2 is "No".
I'd also like some pointers on how to work out how many cells have "GGF" and
"Pass"/"Fail".

The problem I'm hitting at the moment (I think) is that I need to be
flexible (I don't know how many rows are in the column) and using $C:$C (for
instance) seems to be messing up the count.

I've tried:

=COUNT(IF(($C:$C="GGF") & ($G:$G="No), 1, 0))

but that doesn't seem to cut the mustard so to speak.

Any pointers would be a great help to someone in need of some brain soothing
after trying to make Excel do this :-)

Thanks
George

Complicated counting of cells (based on other cells contents)
 

was does the --- represent? I think this solves my prioblem also!

"Duke Carey" wrote:

modify as needed to get all your permutations

=SUMPRODUCT(--(A1:A1000="GGF"),--(B1:B1000="No"))

or, for all 3 columns

=SUMPRODUCT(--(A1:A1000="GGF"),--(B1:B1000="No"), --(C1:c1000="Pass"))


"George" wrote:

Hi,

I'm looking to count the number of cells in a column based on the content in
that cell and the content of another cell.

Column 1 has three possible values (GGF, CGF, SGF) as text.
Column 2 has two possible values (Yes, No) as text.
Column 3 has two possible values (Pass, Fail) as text.

I need to be able to make several counts based on the first column,
referencing the other two columns. I'm going to use three different cells to
display each count to simplify the formulas and to ease reading.

Could someone please help me work the formula out to count the number of
cells in column 1 with the value "GGF" when the value of column 2 is "No".
I'd also like some pointers on how to work out how many cells have "GGF" and
"Pass"/"Fail".

The problem I'm hitting at the moment (I think) is that I need to be
flexible (I don't know how many rows are in the column) and using $C:$C (for
instance) seems to be messing up the count.

I've tried:

=COUNT(IF(($C:$C="GGF") & ($G:$G="No), 1, 0))

but that doesn't seem to cut the mustard so to speak.

Any pointers would be a great help to someone in need of some brain soothing
after trying to make Excel do this :-)

Thanks
George

Complicated counting of cells (based on other cells contents)
 

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ConstructionGuy" wrote in
message ...
was does the --- represent? I think this solves my prioblem also!

"Duke Carey" wrote:

modify as needed to get all your permutations

=SUMPRODUCT(--(A1:A1000="GGF"),--(B1:B1000="No"))

or, for all 3 columns

=SUMPRODUCT(--(A1:A1000="GGF"),--(B1:B1000="No"), --(C1:c1000="Pass"))


"George" wrote:

Hi,

I'm looking to count the number of cells in a column based on the
content in
that cell and the content of another cell.

Column 1 has three possible values (GGF, CGF, SGF) as text.
Column 2 has two possible values (Yes, No) as text.
Column 3 has two possible values (Pass, Fail) as text.

I need to be able to make several counts based on the first column,
referencing the other two columns. I'm going to use three different
cells to
display each count to simplify the formulas and to ease reading.

Could someone please help me work the formula out to count the number
of
cells in column 1 with the value "GGF" when the value of column 2 is
"No".
I'd also like some pointers on how to work out how many cells have
"GGF" and
"Pass"/"Fail".

The problem I'm hitting at the moment (I think) is that I need to be
flexible (I don't know how many rows are in the column) and using
$C:$C (for
instance) seems to be messing up the count.

I've tried:

=COUNT(IF(($C:$C="GGF") & ($G:$G="No), 1, 0))

but that doesn't seem to cut the mustard so to speak.

Any pointers would be a great help to someone in need of some brain
soothing
after trying to make Excel do this :-)

Thanks
George