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

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

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

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