Counting based on other cells contents...

[Guest]

Hi,

I'm after a (fairly) complicated formula here. I have two three columns I'm
interested in. Column 1 contains three different values ("CGF","GGF","SGF"),
column 2 contains "Yes"/"No" and column 3 contains "Pass"/"Fail".

I'd like to count the number of times in the first column ($C:$C as I don't
know how many entries there are) for each value (GGF,CGF,SGF) that have a
"No" in column 2 as well.

I've tried This:

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

but with no luck.

Can anyone give me any pointers as to where I'm going wrong? I didn't want
to have to put a hidden column in as I believe Excel can achieve what I want
without the need for hidden columns.

Once I have this formula licked I should be able to translate it to cope
with each column 1 value, and the "Pass"/"Fail" values as well.

Thanks
George

 

[Guest]

sounds like sum product would work
assuming they three columns are C,D and
=sumproduct(--($C$1:$C$1000="CGF"),--($D$1:$D$1000="Yes"),--($E$1:#E#1000="Pass")
the "--()" changes a logic true false to a numeric 1 0.
the arrays in each section must be the same size but can not be the
shorthand for a full column (C:C won't work)

 

[Guest]

bj,

Thanks very much for the information. I've just located the SUMPRODUCT
formula and it does work albeit with the addition that you said about
shorthand for a column not working. How frustrating!

Nevertheless this has answered the question to a tee. Now all I have to do
is work out what happens if someone puts values in that are outside the
formula range

Thanks for your help...
George

 

[Dave Peterson]

If you have headers in row 1, you could use:

.... --($C$2:$C$65536="CGF"), ...

or just ignore that final row

.... --($C$1:$C$65535="CGF"), ...