Complicated counting of cells (based on other cells contents)

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Bob Phillips]

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

--

HTH

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