Complicated counting of cells (based on other cells contents)

[George]

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

[Duke Carey]

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
>

[ConstructionGuy]

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
> >

[Bob Phillips]

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

--

HTH

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


"ConstructionGuy" <[email protected]> wrote in
message news:[email protected]...
> 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
> > >