Counting based on other cells contents...
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"), ...
George wrote:
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
"bj" wrote:
sounds like sum product would work
assuming they three columns are C,D and E
=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)
"George" wrote:
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
--
Dave Peterson
|